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.
| Event | Old row values | New row values | Typical purpose |
|---|---|---|---|
| INSERT | Not available | Available | Defaults, validation, audit |
| UPDATE | Available | Available | Change history, derived columns |
| DELETE | Available | Not available | Archiving, cascade logic |
| TRUNCATE | Not available | Not available | Statement-level logging only |
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.
| Variant | Runs | Best use | Caution |
|---|---|---|---|
| BEFORE row | Before each row is written | Normalize or validate values | Avoid queries per row on large batches |
| AFTER row | After each row is written | Audit trail per record | Adds write cost to every row |
| AFTER statement | Once per statement | Summary logging, cache invalidation | No direct access to single rows |
| INSTEAD OF | In place of the operation | Updatable views | Must reimplement the full write |
| DDL or event trigger | On schema or login events | Change control, security policy | Can block deployments if faulty |
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.
| Mechanism | Invocation | Strength | Weakness |
|---|---|---|---|
| Constraint | Automatic, declarative | Fast, optimizer-aware, self-documenting | Limited to simple row or key rules |
| Trigger | Automatic, procedural | Handles complex cross-table rules | Hidden logic, harder to debug |
| Stored procedure | Explicit call | Clear control flow, reusable | Can be bypassed by direct writes |
| Application code | Explicit in the client | Easy to test and version | Every client must repeat the rule |
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.
| Symptom | Likely cause | Fix |
|---|---|---|
| Bulk updates suddenly slow | Row-level trigger with per-row queries | Use statement-level logic with transition tables |
| Unexpected data changes | Trigger modifies other tables silently | Document triggers and limit their scope |
| Infinite loop or depth error | Recursive or cascading triggers | Guard recursion and set depth limits |
| Logic works for one row only | SQL Server trigger assumes single row | Process the full inserted and deleted sets |
| Deadlocks under load | Trigger locks rows in other tables | Keep trigger bodies short and ordered |
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.