TRIGGER

Programmability
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

Filter by Database
SQL TRIGGER Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native 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 Native 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 Native all AFTER and INSTEAD OF DML triggers. DDL triggers (for schema changes) since 2005. Uses inserted and deleted pseudo-tables.
Oracle Native all BEFORE/AFTER/INSTEAD OF. Row-level and statement-level. Schema-level and database-level event triggers also supported. Uses :NEW and :OLD.
SQLite Native 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: Native since 5.0.2 PostgreSQL: Native in all listed versions SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Native since 2.5.0

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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;