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]
