Running foreign keys in SQLite
SQLite comes with genfkey utility that will generate triggers to enforce foreign key constraints. Here is the source. There is also a README, just change the previous url to f = sqlite / tool / genfkey.README (stackoverflow only allows me to submit one url)
Two trigger pairs are generated behind FK: BEFORE INSERT AND BEFORE UPDATE on the reference table, and BEFORE DELETE and AFTER UPDATE on the reference table. I cannot understand why the last trigger is AFTER and not BEFORE, like others. See line 741 in the source, or just search for "AFTER", this is the only instance in the file.
It's not a huge deal - if you're in a transaction and the AFTER trigger generates an error, you can still backtrack. I'm just wondering if anyone has any idea why this is different.
Because it needs the ROWID of the inserted row.
The ROWID is generated when a row is inserted, so it will not be available in a BEFORE trigger.
I don't know exactly how this module works, but the ROWID reference is on line 755:
", '/on_update/', on_update"
", '/name/', 'genfkey' || min(rowid)"
", '/tbl/', dq(from_tbl)"
See also: http://linuxgazette.net/109/chirico1.html
Please note that true foreign key constraints are finally supported in the stable release of sqlite, v3.6.19 and later: http://www.sqlite.org/foreignkeys.html
AFTER UPDATE A trigger means that Oracle will fire this trigger after an UPDATE operation.
From Oracle / PLSQL: AFTER UPDATE Trigger after a quick google.
This should be a replication of an ANSI SQL query (I think) that runs after the UPDATE completes. There are BEFORE, AFTER, and INSTEAD OF triggers that SQLite does not support out of the box.