SQLite CREATE TRIGGER

SQLite CREATE TRIGGER is a SQL statement in the SQLite Specific category. SQLite: triggers execute SQL statements automatically in response to data changes. The syntax is CREATE TRIGGER trigger_name [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON table BEGIN ... END;. It returns trigger created. A typical example: CREATE TRIGGER log_employee_insert AFTER INSERT ON employees BEGIN INSERT INTO audit_log (table_name, action, employee_id, changed_at) VALUES ('employees', 'INSERT', NEW.id, datetime('now')); END; CREATE TRIGGER prevent_negative_salary BEFORE UPDATE OF salary ON employees BEGIN SELECT CASE WHEN NEW.salary < 0 THEN RAISE(ABORT, 'Salary cannot be negative') END; END; -- OLD.row: references the row before update/delete -- NEW.row: references the row after insert/update -- Listing triggers: SELECT name FROM sqlite_master WHERE type = 'trigger'; 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.