TRIGGERProgrammability
Buy Me A Coffee

A procedure that fires automatically in response to INSERT, UPDATE, or DELETE events on a table.

Procedural code automatically executed in response to INSERT, UPDATE, or DELETE events on a table. Useful for auditing, derived data maintenance, and enforcing complex integrity rules. Can cause performance issues and make debugging harder -- use with caution.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.0.2 BEFORE/AFTER on INSERT/UPDATE/DELETE. One trigger per event/timing combination per table (before 8.0.16). Uses NEW and OLD row references.
PostgreSQL ✓ Supported all BEFORE/AFTER/INSTEAD OF. Multiple triggers per event allowed. Trigger logic lives in a separate PL/pgSQL FUNCTION returning TRIGGER. Supports both row-level and statement-level triggers.
SQL Server ✓ Supported all AFTER and INSTEAD OF DML triggers. DDL triggers (for schema changes) since 2005. Uses inserted and deleted pseudo-tables.
Oracle ✓ Supported all BEFORE/AFTER/INSTEAD OF. Row-level and statement-level. Schema-level and database-level event triggers also supported. Uses :NEW and :OLD.
SQLite ✓ Supported 2.5.0 BEFORE/AFTER/INSTEAD OF (INSTEAD OF only on views). No statement-level triggers. Uses NEW and OLD row references.

Details

PostgreSQL's trigger model is the most flexible, using separate reusable functions in any supported language. SQL Server triggers work with batches of rows (via inserted/deleted tables) rather than single rows.

Standard Syntax

-- Syntax varies by engine; this is MySQL/SQLite style: CREATE TRIGGER trg_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN SET NEW.created_at = NOW(); END;

Version Support

MySQL: Since 5.0.2 PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since 2.5.0

Per-Database Syntax & Notes

MySQL

BEFORE/AFTER on INSERT/UPDATE/DELETE. One trigger per event/timing combination per table (before 8.0.16). Uses NEW and OLD row references.

CREATE TRIGGER trg BEFORE INSERT ON tbl FOR EACH ROW SET NEW.created_at = NOW();

PostgreSQL

BEFORE/AFTER/INSTEAD OF. Multiple triggers per event allowed. Trigger logic lives in a separate PL/pgSQL FUNCTION returning TRIGGER. Supports both row-level and statement-level triggers.

CREATE FUNCTION set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$; CREATE TRIGGER trg BEFORE UPDATE ON tbl FOR EACH ROW EXECUTE FUNCTION set_updated_at();

SQL Server

AFTER and INSTEAD OF DML triggers. DDL triggers (for schema changes) since 2005. Uses inserted and deleted pseudo-tables.

CREATE TRIGGER trg ON tbl AFTER INSERT AS BEGIN UPDATE tbl SET updated_at = GETDATE() WHERE id IN (SELECT id FROM inserted); END;

Oracle

BEFORE/AFTER/INSTEAD OF. Row-level and statement-level. Schema-level and database-level event triggers also supported. Uses :NEW and :OLD.

CREATE TRIGGER trg BEFORE INSERT ON tbl FOR EACH ROW BEGIN :NEW.created_at := SYSDATE; END;

SQLite

BEFORE/AFTER/INSTEAD OF (INSTEAD OF only on views). No statement-level triggers. Uses NEW and OLD row references.

CREATE TRIGGER trg AFTER INSERT ON tbl BEGIN UPDATE tbl SET updated_at = datetime('now') WHERE id = NEW.id; END;