Foreign Key Triggers in SQLite

Question

SQLite comes with a utility, genfkey, that will generate triggers to enforce foreign key constraints. Here is the source. There is a README as well, just change previous url to f=sqlite/tool/genfkey.README (stackoverflow only letting me post one url)

Two pairs of triggers are generated per FK: BEFORE INSERT and BEFORE UPDATE on referencing table, and BEFORE DELETE and AFTER UPDATE on referenced table. I can't figure out why the last trigger is AFTER instead of BEFORE like the others. See line 741 in the source, or just search for "AFTER", it's the only instance in the file.

Its not a huge deal - if you're in a transaction, and the AFTER trigger generates an error, you can still roll back. I'm just wondering if anybody has any ideas why it's different.

Answer 1

Note that true foreign key constraints are finally supported in a stable release of sqlite, v3.6.19 and later: http://www.sqlite.org/foreignkeys.html

Answer 2

Because it needs the ROWID of the inserted row.

The ROWID is generated when the row is inserted, so it would not be available in a BEFORE trigger.

I don't know exactly how this module works, but ROWID is referenced in line 755:

", '/on_update/', on_update"

", '/name/',   'genfkey' || min(rowid)"
", '/tbl/',    dq(from_tbl)"

See also: http://linuxgazette.net/109/chirico1.html