RE: [sqlite] Foreign Constraint Triggers Across Attached Databases

[Samuel R. Neff]

I've run into two situations recently where I would have preferred to write
triggers across databases.  Both related to audit tracking of data.

The first situation is that for every table, I have a corresponding history
table that records the history of every record.  So let's say I have

CREATE TABLE Users (UserID, UserName);

then I also have

CREATE TABLE Users_History (UserHistoryID, TransactionID, ChangeType,
UserID, UserName)

and to track transactions I have 

CREATE TABLE Transactions(TransactionID, TrasnactionTS, UserID)

and to track the single active transaction I have

CREATE TABLE ActiveTransaction(TransactionID)

which is always blank except when in the middle of a transaction (which
always starts with inserting a record to that table, and then deleting it
right before commit).


So I have triggers on the Users table that whenever a record is inserted,
updated, or deleted, the corresponding new values for insert/update and old
values for delete are inserted into the history table.  The triggers look
like this:

CREATE TRIGGER HI_Users
AFTER INSERT ON Users
FOR EACH ROW BEGIN

        SELECT RAISE(ABORT, 'Can not update database when no transaction is
active.  Create a new transaction in the Transactions table and create an
associated record in the ActiveTransaction table.')
        WHERE (SELECT COUNT(*) FROM ActiveTransaction) = 0;
        
        INSERT INTO 
                Users_History (
                        TransactionID,
                        ChangeType,
                        UserID, UserName
                )
        SELECT
                        (SELECT MIN(TransactionID) FROM ActiveTransaction),
                        'I',
                        NEW.UserID, NEW.UserName
                ;
END;    


Due to the restriction that triggers cannot span databases, I have my main
data tables, history tables, and the ActiveTransaction table all in the same
database.  I'd really rather the history tables be in a separate database
because they can grow quite large and when I ask a customer to e-mail me
their database, I'd like them to be able to easily e-mail the main data only
without the extra history info.

Also, it would be much cleaner if the ActiveTransaction table was in TEMP
instead of in MAIN so each connection clearly has it's own table (except
where now they share the same table definition, just the data is never
shared due to convention of being populated only within a transaction).

I hope these examples are helpful.  I would like to see the ability to
create a trigger that spans database some day and would expect that the
trigger could be defined and simply would error out if at runtime the
required database was not present.

Thanks,

Sam

-------------------------------------------
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-----Original Message-----
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 24, 2008 6:56 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Foreign Constraint Triggers Across Attached Databases


Triggers between two separate databases are not allowed since if
you DETACH one of the databases, the triggers obviously will no
longer work.  If two separate databases are so inseparably bound
that they need triggers between them, why not just make them a
single database?

The same goes for foreign key constraints.  There are *severe*
implementation difficulties trying to get this to work across
separate database.  If you have a foreign key in a separate
database, that really argues that the two databases ought to
be one.



-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------