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

An AFTER UPDATE Trigger means that Oracle will fire this trigger after the UPDATE operation is executed.

From Oracle/PLSQL: AFTER UPDATE Trigger after a fast google.

This must be replicating the ANSI (I think) SQL trigger that gets executed after the UPDATE is finished. There can be BEFORE, AFTER and INSTEAD OF triggers, that SQLite doesn't support out of the box.

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

Answer 3

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