SQL Triggers: The Essential Guide

A SQL trigger is database-defined behavior that runs automatically in response to a supported event, such as inserting, updating, or deleting data. Use one when behavior must run for every qualifying database operation, regardless of which application issued it. First check whether a native constraint can express the rule; triggers add hidden execution paths, and their syntax and behavior differ by database engine and version.

This guide covers PostgreSQL 17/18, SQLite, MySQL 26.7, and SQL Server 17 documentation. Those versions identify the documentation reviewed, not a claim that syntax is interchangeable. Check your deployed engine and version before running examples. The practical rule: decide the event, timing, and row or statement scope first, then test multirow changes, cascades, recursion, privileges, and error handling.

1. When should you use a database trigger?

A trigger is a good fit when an action must happen automatically at the database boundary. For example, you might record an audit row after a real change, maintain a related summary, or enforce a cross-table rule that a constraint cannot express. A trigger can also cover writes from multiple applications or administrative tools, because it responds to the database event rather than a particular application code path.

Prefer a constraint for ordinary integrity rules when the engine can express them with a primary key, foreign key, unique constraint, check constraint, or not-null constraint. Constraints make the rule visible in the schema and are designed to reject invalid data. A trigger is more flexible, but its side effects can be less obvious to the people maintaining queries and applications.

Need	Consider
Reject a row that violates a simple invariant	A native constraint first
Record changes or maintain database-side behavior for all writers	A trigger, with explicit scope and recursion review
Run work later or outside the transaction	An application job or queue may be a better fit
Change how a view operation is implemented	An INSTEAD OF trigger where supported

Before adding one, document the rule, its owner, the event that fires it, its effects on transaction success, and how it will be migrated and tested. Readers should not have to discover a critical business operation by tracing an unexpected write.

2. Timing and scope: what happens when a trigger fires?

Timing

• BEFORE: Runs before the relevant operation completes. Some engines let row triggers adjust the proposed row. Do not assume a change made here is legal or behaves the same on every engine.
• AFTER: Runs after the operation reaches the engine-defined success point. It is commonly used for audit work or related actions that depend on the completed change.
• INSTEAD OF: Runs in place of an operation, commonly for view updates. Availability and supported objects are engine-specific.

Scope

A row-level trigger runs once for each affected row. A statement-level trigger runs once for the SQL statement, whether that statement affects many rows or none (PostgreSQL documents this behavior). These are different execution models, not just alternate spellings.

For instance, a single UPDATE matching 500 rows might run a row trigger 500 times in SQLite, while SQL Server fires its DML trigger once and exposes the affected rows as a set through inserted and deleted. PostgreSQL supports row and statement triggers. Design for the engine’s model and the largest plausible affected set; a trigger that assumes one row can silently miss data or fail on a batch.

3. Engine differences to check before writing SQL

Engine documentation	Scope and timing	Important details
PostgreSQL 17/18	BEFORE, AFTER, INSTEAD OF; row and statement triggers	INSTEAD OF is row-level and for views; TRUNCATE triggers are statement-level. Multiple triggers of the same kind run in name order. A trigger can cover multiple events with OR. Row conditions and transition relations are available in supported forms.
SQLite	BEFORE or AFTER; row triggers only	Supports INSERT, UPDATE, DELETE. No statement triggers. Prefer AFTER when possible; modifying or deleting the target row in a BEFORE UPDATE/DELETE trigger has undefined results. Unknown names in UPDATE OF are silently ignored.
MySQL 26.7	BEFORE or AFTER; each affected row	Multiple triggers can share timing and event. Creation order is the default; FOLLOWS/PRECEDES can set order. Trigger creation captures sql_mode. Definer identity affects privilege checks.
SQL Server 17	AFTER or INSTEAD OF DML triggers; statement invocation	Handle affected rows as sets using inserted/deleted. DDL and logon triggers also exist. TRUNCATE TABLE does not fire a DML trigger.

These are engine-specific behaviors drawn from the linked primary references: PostgreSQL CREATE TRIGGER, PostgreSQL trigger behavior, SQLite CREATE TRIGGER, MySQL CREATE TRIGGER, and SQL Server multirow DML triggers and CREATE TRIGGER reference.

4. Runnable examples: audit only real changes

The following examples illustrate engine-specific syntax for recording updates to an orders table. Adapt names and columns to your schema. They assume an audit table with order_id and changed_at columns. They are not one portable SQL script; execute only the version for your engine after checking its installed version and permissions.

PostgreSQL 17/18

CREATE TABLE order_audit (
  order_id bigint NOT NULL,
  changed_at timestamptz NOT NULL DEFAULT current_timestamp
);

CREATE FUNCTION audit_order_update()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO order_audit (order_id) VALUES (NEW.id);
  RETURN NEW;
END;
$$;

CREATE TRIGGER orders_audit_update
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION audit_order_update();

The WHEN clause compares old and new row values. It avoids an audit row when an update leaves the row unchanged. By contrast, UPDATE OF status checks whether status appeared in the UPDATE target list; it does not prove that the stored value changed. PostgreSQL trigger functions receive event values such as OLD and NEW through trigger context rather than ordinary function arguments.

SQLite

CREATE TABLE order_audit (
  order_id INTEGER NOT NULL,
  changed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER orders_audit_update
AFTER UPDATE ON orders
FOR EACH ROW
WHEN OLD.status IS NOT NEW.status
BEGIN
  INSERT INTO order_audit (order_id) VALUES (NEW.id);
END;

SQLite’s IS NOT comparison handles NULL values in this simple column comparison. Name the actual columns you need to compare. SQLite recommends preferring AFTER triggers: the official language reference says “programmers are encouraged to prefer AFTER triggers over BEFORE triggers.” Also verify every column named in an UPDATE OF clause; misspelled or unknown names are silently ignored.

MySQL 26.7

CREATE TABLE order_audit (
  order_id BIGINT NOT NULL,
  changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER orders_audit_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  IF NOT (OLD.status <=> NEW.status) THEN
    INSERT INTO order_audit (order_id) VALUES (NEW.id);
  END IF;
END//
DELIMITER ;

The null-safe equality operator <=> allows the example to compare nullable status values. MySQL stores the sql_mode active when a trigger is created and uses it when the trigger later runs. Use an intentional session mode during deployment. Check the definer and privileges as part of release review.

SQL Server 17

CREATE TABLE dbo.order_audit (
  order_id bigint NOT NULL,
  changed_at datetime2 NOT NULL DEFAULT sysdatetime()
);
GO

CREATE TRIGGER dbo.orders_audit_update
ON dbo.orders
AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  INSERT INTO dbo.order_audit (order_id)
  SELECT i.id
  FROM inserted AS i
  JOIN deleted AS d ON d.id = i.id
  WHERE i.status <> d.status
     OR (i.status IS NULL AND d.status IS NOT NULL)
     OR (i.status IS NOT NULL AND d.status IS NULL);
END;
GO

SQL Server invokes this trigger for the statement and supplies sets of changed and prior rows. The join assumes id is a stable unique key. The explicit NULL checks make status change detection work when either side can be NULL. The set-based insert handles one row or many without a cursor.

5. Multirow statements, cascades, and recursion

Always test bulk operations. In SQL Server, one statement can affect many rows, and the trigger receives the affected rowsets at once. Use joins or other set-based expressions over inserted and deleted; do not assign a scalar from those sets on the assumption that only one row exists. Microsoft recommends rowset-based logic instead of cursors for multirow work.

In SQLite, a row trigger executes once for each affected row, so work may repeat many times. PostgreSQL lets you choose row or statement scope and provides transition relations in supported trigger forms. Statement scope can be useful for set-oriented work, but confirm which transition data is available for the exact event and timing.

Trigger-issued SQL can fire other triggers. PostgreSQL documents that trigger actions can recurse, with no direct limit on cascade depth. Foreign-key cascade actions also perform ordinary updates or deletes on referencing tables; a trigger that changes or blocks those operations can interfere with referential integrity. Map the entire path—original write, cascading action, triggered write, and any further trigger—then test both valid and invalid cases in a transaction-safe environment.

6. A practical implementation checklist

1. Confirm the engine and version. Record the exact deployed version and consult its official CREATE TRIGGER reference.
2. Try a constraint first. Use a trigger only where the behavior needs procedural or cross-table logic that a constraint cannot express.
3. Choose event, timing, and scope. Specify INSERT, UPDATE, DELETE, or another supported event; decide BEFORE/AFTER/INSTEAD OF and row/statement scope.
4. Define change detection precisely. Decide whether you mean a target column was mentioned or its value actually changed. Handle NULL values deliberately.
5. Make set behavior explicit. Test zero, one, and many affected rows, including updates that change no values.
6. Trace side effects. Review cascades, nested triggers, recursion, and transaction failure behavior.
7. Review security and deployment settings. Check ownership/definer rights, needed privileges, captured session settings, trigger order, and migration rollback.
8. Observe it in production. Log meaningful failures and maintain a schema inventory so hidden write paths remain discoverable.

7. Performance, reliability, and cost

A trigger adds work to the database operation that fires it. A row trigger on a bulk update may execute once per affected row; any extra queries or writes compound that work. Statement triggers, where supported, can operate on sets, but still consume resources. Keep trigger bodies focused, index lookup keys used by their queries, and avoid repeating expensive work that can be computed once per statement or handled asynchronously.

Reliability depends on transaction semantics: in common transactional configurations, a trigger failure can cause the triggering statement or transaction to fail. Confirm the exact behavior for the engine, storage configuration, and statement type you use. Keep external network calls out of trigger bodies; slow or unavailable external services can make database writes unpredictable. A transactional outbox is one pattern for recording work in the database and processing it separately.

There is no universal trigger performance number. Measure using representative batch sizes and realistic indexes, and compare latency, lock duration, write volume, and failure behavior with the trigger enabled. Costs include database capacity, operational complexity, harder debugging, and migration risk—not a separate SQL feature fee in the cited documentation.

8. Troubleshooting common trigger errors

Symptom	Likely cause	What to check
Some rows are missing from an audit or summary	Trigger code assumes one affected row	For SQL Server, process inserted/deleted as sets; test a multirow statement. For row engines, check per-row conditions and errors.
Trigger fires but records an unchanged value	Column-list condition treated as value-change detection	Compare OLD and NEW values with NULL-safe logic. In PostgreSQL, distinguish UPDATE OF from WHEN (OLD.* IS DISTINCT FROM NEW.*).
SQLite trigger silently ignores the intended update	A misspelled column in UPDATE OF	Verify each column name against the table; SQLite does not reject unknown names in this clause.
SQLite BEFORE trigger behaves unpredictably	It modifies or deletes the row being changed	Prefer AFTER and move dependent work there where appropriate.
Trigger fails only in one environment	Different privileges, definer, SQL mode, object owner, or trigger order	Compare deployment identity and engine settings. MySQL captures sql_mode at trigger creation and applies definer privilege rules.
Unexpected repeated writes or recursion	A trigger’s SQL activates another trigger, or a cascade reaches the table	Trace the full trigger and foreign-key path; add conditions to stop no-op work and test recursion carefully.
Expected trigger does not run after TRUNCATE	Engine-specific TRUNCATE semantics	SQL Server DML triggers do not fire for TRUNCATE TABLE. PostgreSQL supports statement-level TRUNCATE triggers. Do not generalize either behavior.
BEFORE trigger cannot repair a bad input value	Type or basic column checks happen before MySQL trigger activation	Validate or transform input earlier; a MySQL BEFORE trigger cannot turn a value invalid for the column type into a valid one.

9. Or skip the browser setup

ScreenshotNeo is a website screenshot API and MCP server from ScreenshotNeo. If you are documenting or monitoring database trigger behavior in a web interface, you can capture a page with one GET request. See the ScreenshotNeo API documentation.

curl -G "https://api.screenshotneo.com/v1/shot" \
  -d access_key=YOUR_API_KEY \
  --data-urlencode url=https://stripe.com \
  -o shot.webp

Cookie banners, popups, and chat widgets are removed before the shot. Bot checks, blank pages, and failed loads are never billed. An MCP server lets AI agents take screenshots. The Free plan includes 1,000 screenshots a month with no card; paid plans start at $5 for 3,000. Sign up free for ScreenshotNeo.

10. FAQ

Are SQL triggers the same in MySQL, PostgreSQL, SQLite, and SQL Server?

No. Timing choices, row versus statement scope, supported objects, trigger order, security, and TRUNCATE behavior differ. Treat trigger SQL as engine-specific and verify it against your deployed version.

What is the difference between BEFORE and AFTER triggers?

BEFORE runs ahead of the engine’s relevant operation point and may allow changes to proposed data, subject to engine rules. AFTER runs after the engine-defined successful work. Exact visibility and constraint timing differ, so use the reference for your engine.

Can a trigger run when an UPDATE changes no values?

It can, depending on its event and condition. If you only want a record for actual value changes, compare old and new values with NULL-safe semantics appropriate to the engine.

Does a trigger run for every row in a bulk statement?

SQLite row triggers run once per affected row; PostgreSQL supports row and statement triggers. SQL Server fires its DML trigger once for the statement and provides affected rows as sets. MySQL triggers run per affected row.

How do I remove or change a trigger?

Use the engine’s corresponding DROP TRIGGER syntax or migration mechanism after reviewing dependencies and deployment permissions. Trigger DDL varies, so consult the official reference linked above rather than copying a command between engines.