Skip to content
Tutorials Center
My learning

Topics

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.

SQL
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

SQL An audit trigger ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL A BEFORE trigger that rejects bad data (fails on purpose) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
SQL
-- 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, CHECK and 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.