G'day, I seem to recall (but cannot find) an email on this mailing list sent by drh quite sometime ago regarding triggers before commits. As I recall, he was battling with some kind of problem for which a trigger that executed immediately before the commit was executed seemed to be the best solution.
I'm currently working on/planning/thinking about an accounting system. The system essentially consists of a transaction table, an account table, and a transaction entry table as follows: CREATE TABLE AccountTable (AccountId INTEGER PRIMARY KEY, name); CREATE TABLE TransactionTable (TransactionId INTEGER PRIMARY KEY, date, memo); CREATE TABLE TransactionEntryTable (TransactionId, AccountId, amount); I'm wanting to put some foreign key constraints in, and I think I can do that with triggers, but the tricky constraint of the data model is that sum(TransactionEntryTable.amount) must always equal zero for each TransactionId (this is double-entry bookkeeping). My thinking is that I can check this with something approximating the following pseudo-triggers and table: CREATE TABLE DirtyTransactions (TransactionId UNIQUE); CREATE TRIGGER addDirtyTransaction BEFORE INSERT OR UPDATE OR DELETE ON TransactionEntryTable BEGIN INSERT OR REPLACE INTO DirtyTransactions VALUES(old.TranactionId) INSERT OR REPLACE INTO DirtyTransactions VALUES(new.TranactionId) END; CREATE TRIGGER checkDirtyTransactions BEFORE COMMIT BEGIN SELECT RAISE(Abort) WHERE (SELECT sum(Amount) AS s FROM DirtyTransactions LEFT INNER JOIN TransactionEntryTable WHERE s<>0 GROUP BY TransactionId); ERASE FROM DirtyTransactions; END; So, did the BEFORE COMMIT get implimented? If so, is it stable/usable? I don't see it on the sqlite language page. If not, can anyone think of an alternative way to do this that doesn't require a table-scan of TransactionEntryTable each time a new transaction is added? Benjamin. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]