Skip to content
Tutorials Center
My learning

Topics

Lesson 80 of 125 ยท Designing Databases

Triggers

Learn SQL triggers with runnable examples. Write BEFORE, AFTER and INSTEAD OF triggers, build audit logs, validate data and keep totals in sync automatically.

A trigger is a piece of SQL that the database runs automatically when something happens to a table: a row is inserted, updated or deleted. You don't call a trigger; you attach it to a table, and from then on it fires every time the event occurs, no matter which application, script or person changed the data.

That "no matter who" is the reason triggers exist. An application can forget to write an audit record or to update a cached total. A trigger can't forget. Triggers are great for audit logs, for keeping derived data in sync, for rules that a CHECK constraint can't express, and for making views writable.

They also have a reputation for causing surprises, because they hide work inside ordinary-looking statements. In this lesson you'll learn the syntax, write the four most common kinds of triggers, see how other databases differ, and learn when to reach for something simpler.

Your first trigger: a price history

An online shop wants to remember every price change. Instead of trusting every piece of code that updates prices, you put the rule into the database. Run this, then try changing more prices:

SQL Recording price changes

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

Three history rows appear: Desk Lamp from 39 to 34, then both products' 10% increase. The second UPDATE set the notebook's price to the value it already had, so the WHEN condition skipped it. Note that the multi-row UPDATE fired the trigger twice, once per row: that's what FOR EACH ROW means.

Anatomy of a trigger

Here is the full shape of a trigger in SQLite (the standard and other databases are similar; differences come later):

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;
  statement;
END;
  • Timing. BEFORE runs before the row is changed, which is the place to validate. AFTER runs once the change is made, which is the place to react: log it, update other tables. INSTEAD OF (views only) replaces the change entirely.
  • Event. INSERT, UPDATE or DELETE. UPDATE OF price, stock narrows an update trigger to statements that set those columns.
  • FOR EACH ROW. SQLite only has row-level triggers, so this is implied even if you leave it out.
  • WHEN. An optional condition. If it's false for a row, the body doesn't run for that row.
  • Body. One or more INSERT, UPDATE, DELETE or SELECT statements between BEGIN and END.

Inside the body, two pseudo-rows give you the data being changed:

Event OLD (row before) NEW (row after)
INSERT not available the inserted row
UPDATE the row before the change the row after the change
DELETE the deleted row not available

Triggers run inside the statement's transaction. If anything in the trigger fails, the statement that fired it fails too, and its changes are undone. An audit row can never be saved for an update that didn't happen, and vice versa.

Validating data with BEFORE and RAISE

CHECK constraints can only look at the row being written. Some rules need to look elsewhere: "a workshop can't take more people than the room holds" depends on how many registrations already exist. A BEFORE INSERT trigger can check that and stop the statement with RAISE:

SQL Enforcing a capacity limit

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

The first two registrations succeed, and the third stops with Error: This workshop is full. RAISE can only be used inside a trigger, and it comes in four flavors:

Form Effect
RAISE(ABORT, 'message') Undo the current statement and report the error (the usual choice)
RAISE(FAIL, 'message') Stop with an error but keep changes the statement already made to earlier rows
RAISE(ROLLBACK, 'message') Roll back the whole transaction
RAISE(IGNORE) Silently skip the current row and carry on

Triggers don't make race conditions disappear everywhere. In SQLite only one connection can write at a time, so this check is safe. In PostgreSQL or MySQL, two transactions could both count one free seat at the same moment and both insert. There you'd lock the workshop row first (SELECT ... FOR UPDATE) or use a stricter isolation level.

Keeping totals in sync

Normalization showed how a cached total drifts when someone forgets to update it. Triggers are the classic cure: one trigger per event keeps the cached value correct no matter how the lines change. Because a line can be inserted, changed or deleted, you need three triggers:

SQL An order total maintained by triggers

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

Order 1 ends at 56.5 (five notebooks at 4.50 plus a 34.00 lamp) and order 2 at 0 after its only line was deleted, matching the computed column. The update trigger subtracts the old line and adds the new one by order id, so it even handles a line that moves to a different order.

Using NEW and OLD deltas is fast, but if a bug ever puts the cache out of step, the error stays forever. A sturdier alternative is to recompute: SET items_total = (SELECT COALESCE(SUM(quantity * unit_price), 0) FROM order_lines WHERE order_id = NEW.order_id). It costs one small aggregate per change and heals itself. There's a second reason to prefer it: money stored as REAL picks up tiny rounding errors, and a long chain of additions and subtractions lets them pile up. Recomputing (or storing integer cents) avoids that.

Audit logs and timestamps

An audit trail records what changed and when. Combining OLD and NEW with SQLite's JSON functions gives you a compact, general log. The same example keeps an updated_at column current. Note how UPDATE OF and WHEN stop the timestamp trigger from reacting to its own update:

SQL An audit trail and updated_at

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

Amara's row now has a timestamp, and the log holds an update row (old and new email) and a delete row for Kenji. The ->> operator pulls values out of the stored JSON; see Working with JSON.

Why doesn't the timestamp trigger loop forever? Its own UPDATE only sets updated_at, and the trigger is declared UPDATE OF name, email, so it doesn't fire again. SQLite also has recursive triggers turned off by default (PRAGMA recursive_triggers). In databases that support BEFORE UPDATE row triggers that modify NEW (PostgreSQL, MySQL), you'd simply assign NEW.updated_at instead, with no second UPDATE at all.

SQLite has no "current user". It's an embedded library without logins, so an audit trigger can't record who made a change. Server databases can: PostgreSQL has current_user, MySQL CURRENT_USER(), SQL Server SUSER_SNAME(). Applications often pass the app-level user id in through a column or a session setting.

INSTEAD OF triggers: writable views

A view that joins tables can't be updated directly, because the database doesn't know which underlying table should change. An INSTEAD OF trigger tells it. The trigger runs instead of the insert, update or delete on the view and performs the real changes:

SQL Inserting through a view

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

The view now accepts inserts by department name. Tariq joins the existing Engineering department, and the Design department is created on the fly for Lucia.

Managing triggers

Triggers are schema objects, so they live in sqlite_schema next to tables, indexes and views. You can list them, and you remove them with DROP TRIGGER. There's no ALTER TRIGGER in SQLite; to change one, drop it and create it again (inside a transaction, so no change slips through in between).

SQL Listing and dropping triggers

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

After notes_ai is dropped, the insert leaves no trace, but the delete is still logged. Dropping a table also drops its triggers.

Triggers in other databases

The idea is the same everywhere, but the syntax and capabilities differ a lot:

SQLite PostgreSQL MySQL / MariaDB SQL Server
Row-level triggers Yes (only kind) Yes Yes (only kind) No
Statement-level triggers No Yes No Yes (only kind)
BEFORE Yes Yes Yes No (use INSTEAD OF)
INSTEAD OF On views On views No Tables and views
Body SQL statements A separate trigger function (PL/pgSQL etc.) Compound statement T-SQL batch
Changed rows OLD / NEW OLD / NEW (or transition tables) OLD / NEW inserted / deleted tables
Can modify NEW in BEFORE No Yes Yes n/a

In PostgreSQL the logic lives in a function, and the trigger only connects the function to a table:

SQL
-- PostgreSQL
CREATE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER customers_set_updated_at
BEFORE UPDATE ON customers
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

SQL Server triggers fire once per statement, and the affected rows arrive as the inserted and deleted tables. A trigger that assumes one row breaks on multi-row updates, which is a classic bug:

SQL
-- SQL Server
CREATE TRIGGER trg_products_price ON products
AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  INSERT INTO price_history (product_id, old_price, new_price)
  SELECT d.id, d.price, i.price
  FROM inserted AS i
  JOIN deleted AS d ON d.id = i.id
  WHERE i.price <> d.price;
END;

MySQL writes the body as a compound statement and needs a temporary delimiter in command-line clients, because the body contains semicolons:

SQL
-- MySQL
DELIMITER //
CREATE TRIGGER customers_before_update
BEFORE UPDATE ON customers
FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END//
DELIMITER ;

DDL, logon and event triggers

Everything so far reacted to data changes: INSERT, UPDATE, DELETE. Some databases can also fire triggers on structure changes (CREATE, ALTER, DROP) and on sessions (someone logging in). They are mostly used for three jobs:

  • Auditing schema changes: who dropped that column, and when?
  • Guardrails: refuse DROP TABLE on a production database, or force a naming convention.
  • Session control: limit logins to certain hours or hosts.

SQLite and MySQL have no such triggers, so the examples below are read-only. What SQLite does give you is a counter that goes up on every structure change, which is the raw material for detecting drift:

SQL SQLite counts schema changes (but cannot trigger on them)

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

SQL Server: DDL and logon triggers

A DDL trigger is scoped to a database (ON DATABASE) or the whole server (ON ALL SERVER) and names the events it listens for. Inside it, the EVENTDATA() function returns XML describing the statement: login, time, object name and the exact command text.

SQL
-- SQL Server: record every table change in an audit table
CREATE TABLE schema_audit (
  id         INT IDENTITY PRIMARY KEY,
  happened   DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  login_name SYSNAME   NOT NULL,
  event_type NVARCHAR(100),
  object_name NVARCHAR(256),
  command    NVARCHAR(MAX)
);
GO
CREATE TRIGGER trg_audit_ddl
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @e XML = EVENTDATA();
  INSERT INTO schema_audit (login_name, event_type, object_name, command)
  VALUES (
    @e.value('(/EVENT_INSTANCE/LoginName)[1]',  'SYSNAME'),
    @e.value('(/EVENT_INSTANCE/EventType)[1]',  'NVARCHAR(100)'),
    @e.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(256)'),
    @e.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)')
  );
END;

DDL triggers run after the change but inside its transaction, so ROLLBACK undoes the statement. That makes a simple safety net:

SQL
-- SQL Server: nobody can drop a table without disabling this first
CREATE TRIGGER trg_no_drop_table
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
  RAISERROR ('Dropping tables is disabled. Disable trg_no_drop_table first.', 16, 1);
  ROLLBACK;
END;

A logon trigger fires when a session is established, before it can run any query. Its classic use is rejecting logins that break a rule. Be careful: a bug in a logon trigger can lock everyone out, so test it with a spare login and know how to connect through the dedicated administrator connection (DAC) to remove it.

SQL
-- SQL Server: only allow the reporting login during working hours
CREATE TRIGGER trg_logon_hours
ON ALL SERVER
FOR LOGON
AS
BEGIN
  IF ORIGINAL_LOGIN() = 'report_user'
     AND DATEPART(HOUR, GETDATE()) NOT BETWEEN 7 AND 19
    ROLLBACK;   -- rejects the connection
END;

PostgreSQL: event triggers

PostgreSQL calls them event triggers. They fire on ddl_command_start, ddl_command_end, sql_drop or table_rewrite, can be filtered by command tag, and call a function declared RETURNS event_trigger. Creating one requires a superuser.

SQL
-- PostgreSQL: log every finished DDL command
CREATE TABLE ddl_log (
  id         bigserial PRIMARY KEY,
  happened   timestamptz NOT NULL DEFAULT now(),
  username   text NOT NULL DEFAULT current_user,
  command    text,
  object     text
);

CREATE FUNCTION log_ddl() RETURNS event_trigger
LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
    INSERT INTO ddl_log (command, object)
    VALUES (r.command_tag, r.object_identity);
  END LOOP;
END;
$$;

CREATE EVENT TRIGGER log_ddl_trg
ON ddl_command_end
EXECUTE FUNCTION log_ddl();

To block a command, raise an exception from a ddl_command_start trigger, optionally limited with WHEN TAG IN ('DROP TABLE'):

SQL
-- PostgreSQL
CREATE FUNCTION forbid_drop() RETURNS event_trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'command % is disabled', tg_tag;
END;
$$;

CREATE EVENT TRIGGER no_drop_table
ON ddl_command_start
WHEN TAG IN ('DROP TABLE')
EXECUTE FUNCTION forbid_drop();

PostgreSQL has no logon trigger in core (version 17 added login event triggers, which come with the same lock-out risk as in SQL Server). Oracle offers both kinds as well: AFTER DDL ON SCHEMA or ON DATABASE, and AFTER LOGON ON DATABASE.

Cautions

  • A schema trigger is a global guardrail. Document it, because developers will be baffled when a migration fails with a custom error.
  • Keep them tiny. A slow or failing DDL trigger blocks every deployment.
  • Superusers can bypass or disable them (DISABLE TRIGGER, ALTER EVENT TRIGGER ... DISABLE), so they protect against accidents, not against attackers. For real access control use permissions.
  • Where triggers do not exist (SQLite, MySQL), put the control in your migration process and database permissions instead.

When not to use a trigger

Triggers are powerful but invisible. A teammate reading DELETE FROM customers WHERE id = 2 has no idea it also writes to a log. Use them deliberately:

  • Prefer declarative constraints. NOT NULL, UNIQUE, CHECK, foreign keys with ON DELETE and generated columns are simpler, faster and self-documenting. Use a trigger only when they can't express the rule.
  • Keep triggers short and boring. No business workflows, no sending emails (a trigger can't anyway), no long chains where one trigger fires another.
  • Mind bulk operations. A row-level trigger runs once per row; updating a million rows runs it a million times.
  • Don't depend on firing order. If two triggers react to the same event, make them independent of each other.
  • Document and test them. Name them consistently (table_event like order_lines_ai), keep them in your migrations, and write tests that prove they fire.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. You want to reject an insert when a related table says there's no room left. Which trigger fits best?

    BEFORE triggers are the place to validate, and RAISE(ABORT) cancels the statement with an error. SQLite doesn't allow subqueries in CHECK constraints, and INSTEAD OF only works on views.
  2. 2. Inside a DELETE trigger, which pseudo-row holds the deleted data?

    OLD is the row before the change. A delete has no NEW row. (SQL Server uses a deleted table instead.)
  3. 3. UPDATE products SET price = price * 2; changes 50 rows. How many times does an AFTER UPDATE ... FOR EACH ROW trigger without a WHEN clause run in SQLite?

    Row-level triggers run once per affected row. SQL Server, by contrast, runs its statement-level triggers once per statement.
  4. 4. What is an INSTEAD OF trigger mainly used for?

    An INSTEAD OF trigger replaces the write on the view with your own statements against the underlying tables.
  5. 5. A statement fires a trigger, and the trigger's INSERT fails. What happens to the original statement's changes?

    A trigger is part of the statement that fired it. If the trigger fails, the whole statement fails and its changes are undone.

Summary

  • A trigger runs SQL automatically on INSERT, UPDATE or DELETE, for every row the statement touches (SQLite has row-level triggers only).
  • BEFORE triggers validate (with RAISE), AFTER triggers react (logs, totals), and INSTEAD OF triggers make views writable.
  • OLD and NEW hold the row before and after the change; UPDATE OF and WHEN limit when a trigger fires.
  • Classic uses: audit logs, cached totals and counters, cross-table rules, timestamps.
  • Triggers run inside the statement's transaction, so a failing trigger undoes the whole change.
  • Other databases differ: PostgreSQL calls trigger functions, SQL Server has statement-level triggers with inserted/deleted tables, MySQL can modify NEW in BEFORE triggers.
  • SQL Server (DDL and logon triggers) and PostgreSQL (event triggers) can also react to CREATE/ALTER/DROP and sessions, for schema auditing and guardrails; SQLite and MySQL cannot.
  • Prefer constraints when they're enough, and keep triggers small, documented and tested.

Next: Reading Query Plans, where you'll start the Performance chapter by watching how the database actually runs your queries.