CODEWITHSUNDEEP
sqlitetriggersforeign-keys
Ryan McKay
Ryan McKay

Reputation: 401

Foreign Key Triggers in SQLite

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.

Upvotes: 2

Views: 2158

Answers (3)

Roman Starkov
Roman Starkov

Reputation: 61482

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

Upvotes: 6

finnw
finnw

Reputation: 48639

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

Upvotes: 2

Esteban Küber
Esteban Küber

Reputation: 36852

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.

Upvotes: 1

Related Questions