SQL Reference ยท Data Definition
SQL CREATE TRIGGER statement
Reference for SQL CREATE TRIGGER: run SQL automatically before or after INSERT, UPDATE and DELETE, OLD and NEW rows, and the dialect differences.
A trigger is a block of SQL that the database runs automatically when a table is changed by an INSERT, UPDATE or DELETE. Triggers enforce rules that plain constraints cannot express, keep audit logs and maintain derived data.
Inside the trigger you can read the row being changed: NEW holds the incoming values (insert, update) and OLD holds the previous values (update, delete). Because they run silently, use them sparingly; hidden logic is hard to debug.
CREATE TRIGGER log_price_change AFTER UPDATE OF price ON products BEGIN INSERT INTO price_log (product_id, old_price, new_price) VALUES (OLD.id, OLD.price, NEW.price); END;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
CREATE TRIGGER [IF NOT EXISTS] trigger_name {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE [OF column, ...] | DELETE} ON table_name [FOR EACH ROW] [WHEN condition] BEGIN statement; ... END;
| Part | Description |
|---|---|
BEFORE / AFTER |
When the trigger fires relative to the change. |
INSTEAD OF |
Replaces the operation; used on views. |
UPDATE OF column |
Fire only when the listed columns are in the SET list. |
FOR EACH ROW |
Fire once per affected row. SQLite always works this way. |
WHEN condition |
Optional filter; may use OLD and NEW. |
| body | One or more statements. RAISE(ABORT, 'message') cancels the operation in SQLite. |
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | Row-level only; body is inline SQL; RAISE() to abort. Drop with DROP TRIGGER. |
| PostgreSQL | Two steps: write a trigger function (RETURNS trigger, PL/pgSQL), then CREATE TRIGGER ... EXECUTE FUNCTION fn(). Row and statement level. |
| MySQL | FOR EACH ROW required; SIGNAL SQLSTATE '45000' raises errors; set DELIMITER in clients for multi-statement bodies. |
| SQL Server | Statement-level AFTER/INSTEAD OF triggers; rows are in the inserted and deleted tables, not OLD/NEW. |
| Oracle | PL/SQL bodies; :OLD and :NEW; BEFORE/AFTER row and statement triggers. |
-- PostgreSQL CREATE FUNCTION log_price() RETURNS trigger AS $$ BEGIN INSERT INTO price_log VALUES (OLD.id, OLD.price, NEW.price); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER log_price_change AFTER UPDATE OF price ON products FOR EACH ROW EXECUTE FUNCTION log_price();
Usage notes
- Use constraints first.
NOT NULL,CHECKand foreign keys are clearer and faster than a trigger doing the same job. - Triggers add cost to every write and can cascade: a trigger's changes may fire other triggers.
- Keep them deterministic and short; avoid logic that depends on the order triggers run.
- Bulk loads fire a trigger per row; this can dominate load time.
- Document them. A trigger is invisible in application code, so list them in your schema notes.
- Remove with
DROP TRIGGER [IF EXISTS] name;. Dropping the table drops its triggers.
Related
CREATE VIEW,CREATE TABLE,CHECK,INSERT,UPDATE- Lessons: Triggers, Stored procedures