RECURSIVE TRIGGER (SQLite)

RECURSIVE TRIGGER (SQLite) is a SQL statement in the SQLite Specific category. SQLite: when enabled, triggers can fire recursively (trigger on a table fires another trigger on the same table). The syntax is PRAGMA recursive_triggers = ON;. It returns recursive trigger mode. A typical example: PRAGMA recursive_triggers = ON; CREATE TRIGGER maintain_count AFTER INSERT ON items BEGIN UPDATE stats SET item_count = item_count + 1 WHERE id = 1; END; CREATE TRIGGER audit_update AFTER UPDATE ON stats BEGIN INSERT INTO audit_log (action, old_count) VALUES ('count_update', OLD.item_count); END; -- INSERT on items -> trigger updates stats -> trigger logs to audit -- Without recursive_triggers, the chain stops at the first cascade A close relative is AUTOINCREMENT (SQLite), which sQLite: unlike AUTO_INCREMENT in MySQL, only INTEGER PRIMARY KEY columns auto-increment. AUTOINCREMENT guarantees unique increasing IDs. A close relative is ROWID / _ROWID_, which sQLite: every row has an implicit 64-bit ROWID. INTEGER PRIMARY KEY becomes an alias for ROWID.