Database trigger

On this page 7

A database trigger is a named block of procedural code stored in the database that the database management system executes automatically in response to a defined event, such as an INSERT, UPDATE or DELETE on a table. In databases, triggers belong to the core server-side mechanisms that enforce rules and react to data changes without relying on the application to call them.

Triggers are defined in the SQL standard and implemented, with dialect differences, by PostgreSQL, Oracle Database, SQL Server, MySQL and SQLite. They run inside the same transaction as the statement that fired them, which gives them strong consistency guarantees and also makes them a frequent source of hidden performance costs.

How a database trigger fires

A trigger definition binds three elements: the triggering event, the timing and the action body. When a statement causes the event on the target table, the engine evaluates any optional WHEN condition and, if it holds, runs the body. Because the body executes within the caller’s transaction, a failure or an explicit error in the trigger rolls back the original statement.

Inside the body, row-level triggers can read the transition values of the affected row. PostgreSQL and Oracle expose them as NEW and OLD records, while SQL Server provides the virtual tables inserted and deleted, which hold all affected rows of the statement at once.

The following table shows which transition values are available for each event.

EventOld row valuesNew row valuesTypical purpose
INSERTNot availableAvailableDefaults, validation, audit
UPDATEAvailableAvailableChange history, derived columns
DELETEAvailableNot availableArchiving, cascade logic
TRUNCATENot availableNot availableStatement-level logging only
Transition values by triggering event

Timing and granularity of database triggers

Trigger timing decides when the body runs relative to the change. A BEFORE trigger runs before the row is written and can modify or reject the incoming values. An AFTER trigger runs once the change is applied and suits auditing or propagating changes. An INSTEAD OF trigger replaces the operation entirely and is mainly used to make a database view updatable.

Granularity determines how often the body executes. A row-level trigger (FOR EACH ROW) runs once per affected row, while a statement-level trigger runs once per statement, even if that statement touches zero or a million rows. SQL Server supports only statement-level DML triggers, which is a common surprise for developers moving from Oracle or PostgreSQL.

VariantRunsBest useCaution
BEFORE rowBefore each row is writtenNormalize or validate valuesAvoid queries per row on large batches
AFTER rowAfter each row is writtenAudit trail per recordAdds write cost to every row
AFTER statementOnce per statementSummary logging, cache invalidationNo direct access to single rows
INSTEAD OFIn place of the operationUpdatable viewsMust reimplement the full write
DDL or event triggerOn schema or login eventsChange control, security policyCan block deployments if faulty
Trigger variants and their best use

Triggers compared with constraints and stored procedures

A trigger is one of several ways to keep data correct, and it is rarely the first choice. A declarative database constraint, such as a foreign key, CHECK or UNIQUE constraint, is simpler, faster and visible to the query optimizer. Referential integrity should be enforced with foreign keys wherever the rule fits that model.

A stored procedure contains similar procedural code but runs only when called explicitly. Triggers are therefore implicit: they execute whether or not the developer remembers them, which is their main strength for mandatory rules and their main weakness for maintainability.

MechanismInvocationStrengthWeakness
ConstraintAutomatic, declarativeFast, optimizer-aware, self-documentingLimited to simple row or key rules
TriggerAutomatic, proceduralHandles complex cross-table rulesHidden logic, harder to debug
Stored procedureExplicit callClear control flow, reusableCan be bypassed by direct writes
Application codeExplicit in the clientEasy to test and versionEvery client must repeat the rule
Choosing between triggers and related mechanisms

Practical uses of database triggers

Triggers work best for rules that must hold regardless of which client writes the data. The most widespread use is an audit trail: an AFTER UPDATE or DELETE trigger copies the old row, the user name and a timestamp into a history table. Because it runs inside the transaction, the audit record is guaranteed to commit together with the change.

  • Maintaining derived values, such as a last-modified timestamp or a denormalized order total.
  • Enforcing complex business rules that span several tables and cannot be expressed as constraints.
  • Capturing changes for replication or an outbox table that downstream services consume.
  • Making views writable with INSTEAD OF triggers that route writes to the base tables.

In OLTP systems, many teams now prefer log-based change data capture over triggers for replication, because reading the write-ahead log adds no extra work to the user transaction.

Risks and common mistakes with triggers

The most serious risk is invisible cost. A trigger that runs a query per row can turn a fast bulk update into a slow, lock-heavy operation, and the statement plan shown to developers often omits the trigger work. The table below lists typical symptoms and fixes.

SymptomLikely causeFix
Bulk updates suddenly slowRow-level trigger with per-row queriesUse statement-level logic with transition tables
Unexpected data changesTrigger modifies other tables silentlyDocument triggers and limit their scope
Infinite loop or depth errorRecursive or cascading triggersGuard recursion and set depth limits
Logic works for one row onlySQL Server trigger assumes single rowProcess the full inserted and deleted sets
Deadlocks under loadTrigger locks rows in other tablesKeep trigger bodies short and ordered
Trigger problems, causes and fixes

A sound rule is to keep trigger bodies short, deterministic and free of external calls. Sending email or calling a web service from a trigger couples the transaction to an outside system and can hold locks for seconds.

The short version

A database trigger is an automatic reaction built into the database. Whenever someone adds, changes or removes data in a certain table, the database runs a small piece of prepared code on its own.

It works like a motion sensor light. Nobody has to press a switch, because the light turns on whenever someone walks by. In the same way, a trigger can record who changed a price or block a change that breaks a rule, even if the program that made the change forgot about that rule.

Where it makes a difference

A hospital pharmacy system stores medication stock in a single table that four different applications update: the dispensing robot, the ward ordering portal, a nightly import job and an administrative tool. Regulations require a complete history of every quantity change, including who made it and when.

Instead of adding logging code to all four applications, the database team creates an AFTER UPDATE row-level trigger on the stock table. It writes the old quantity, the new quantity, the session user and the transaction timestamp to an audit table. When the nightly import later updates about 40,000 rows, the team notices the job takes longer and rewrites the trigger as a statement-level trigger using transition tables. The import time returns to normal, and auditors receive a complete, tamper-resistant history regardless of which application made the change.