Triggers
Trigger Concept
Read aloud is unavailable in this browser.
Reactive Programming in Databases
Imagine you need to ensure that every time an employee's salary is updated, the change is logged with the old value, new value, timestamp, and the user who made the change. Additionally, if anyone tries to delete a customer record, you want to automatically archive it first. And when a new order is inserted, you need to update inventory counts across multiple tables.
You could implement all this logic in your application code—but then every developer, every application, and every interface that touches your database must remember to execute these side effects. Miss one, and your data integrity crumbles.
Triggers are the database's answer to this problem. They are reactive programming constructs that fire automatically in response to data modification events, ensuring that critical business logic executes regardless of how or who modifies the data.
By the end of this page, you will understand what triggers are, how they fit into the database architecture, their historical evolution, essential terminology, and when triggers are the right (or wrong) tool for the job. This conceptual foundation is essential before exploring trigger syntax and advanced patterns.
What Is a Trigger?
A trigger is a named database object containing procedural code that the database management system (DBMS) automatically executes (or "fires") in response to specific events on a particular table or view. Unlike stored procedures—which must be explicitly invoked—triggers are implicit and event-driven.
Formal Definition:
A trigger is a procedural structure that specifies:
- The triggering event — An INSERT, UPDATE, DELETE (or combinations) on a specific table
- The activation time — Whether to execute BEFORE or AFTER the event (some systems also support INSTEAD OF)
- The granularity — Whether to fire once per affected row (row-level) or once per statement (statement-level)
- The trigger body — The procedural code to execute when conditions are met
This creates an Event-Condition-Action (ECA) model:
- Event: A data modification occurs (INSERT, UPDATE, DELETE)
- Condition: Optional logic that further filters when the trigger body runs
- Action: The procedural code that executes
The ECA paradigm traces back to active database research in the 1980s and 1990s, and modern triggers are the practical implementation of this theoretical framework.
12345678910111213
-- Conceptual structure of a trigger (pseudo-SQL)CREATE TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} -- Activation time {INSERT | UPDATE | DELETE} -- Triggering event(s) ON table_name -- Target table [REFERENCING OLD AS old_alias NEW AS new_alias] -- Row references [FOR EACH ROW | FOR EACH STATEMENT] -- Granularity [WHEN (condition)] -- Optional condition BEGIN -- Trigger body: procedural code -- Can access OLD and NEW row values -- Can perform additional DML, validation, etc. END;Declarative constraints (PRIMARY KEY, FOREIGN KEY, CHECK) are always preferred for simple integrity rules because the optimizer understands them and they're more concise. Triggers are for procedural logic that constraints cannot express—like logging, complex business rules, or cross-table side effects.
Historical Context and Evolution
Understanding triggers' historical evolution illuminates why they exist and how they've matured across database systems.
Early Databases (1970s-1980s):
The relational model proposed by E.F. Codd focused on declarative operations—SELECT, PROJECT, JOIN—with no native mechanism for reactive behavior. Early systems relied entirely on application-layer logic to enforce business rules beyond basic constraints.
Active Database Research (1980s-1990s):
Academic research on "active databases" explored systems that could react automatically to events. The Event-Condition-Action (ECA) model formalized this:
- Systems like HiPAC (High-Performance Active DBMS) and Starburst pioneered trigger semantics
- Research explored complex event patterns, temporal conditions, and rule interaction
SQL Standard Adoption:
The SQL:1999 standard (SQL3) formally introduced triggers to the ANSI specification, though commercial implementations preceded the standard:
- Oracle: Triggers since version 6 (1988)
- Sybase/SQL Server: Triggers since the late 1980s
- PostgreSQL: Trigger support added in version 6.0 (1997)
- MySQL: Triggers added in version 5.0 (2005)
Modern Refinements:
Contemporary systems have extended basic trigger functionality with:
- Compound triggers (Oracle) — Multiple timing points in a single trigger
- INSTEAD OF triggers — For making views updatable
- DDL triggers — Responding to schema changes (CREATE, ALTER, DROP)
- Logon/logoff triggers — Database session events
- Event triggers (PostgreSQL) — For global database events
| DBMS | Initial Trigger Support | Notable Extensions |
|---|---|---|
| Oracle | Version 6 (1988) | Compound triggers, system triggers, INSTEAD OF |
| SQL Server | Sybase era (~1989) | DDL triggers, logon triggers, INSTEAD OF |
| PostgreSQL | Version 6.0 (1997) | Event triggers, constraint triggers, transition tables |
| MySQL | Version 5.0 (2005) | Multiple triggers per event (5.7+), but no statement-level |
| DB2 | Early 1990s | Advanced row/statement triggers, inlined triggers |
| SQLite | Version 2.5 (2002) | Minimal but functional, FOR EACH ROW only |
While the SQL standard defines trigger basics, each vendor has significant syntactic and semantic differences. Code from one DBMS rarely ports directly to another. Always consult vendor-specific documentation and note that examples in this module use PostgreSQL/Oracle-like syntax that may require adaptation.
Architectural Role of Triggers
To truly understand triggers, we must situate them within the broader DBMS architecture. Triggers exist at the intersection of the query processor, storage engine, and procedural execution environment.
Where Triggers Execute in the Query Pipeline:
When a DML statement (INSERT/UPDATE/DELETE) is processed:
- Parse & Optimize — The SQL statement is parsed and an execution plan is generated
- Trigger Discovery — The system identifies triggers associated with the target table and event
- BEFORE Trigger Execution — Pre-event triggers fire, potentially modifying the row before storage
- DML Execution — The actual data modification occurs in the storage engine
- Constraint Checking — Referential integrity and check constraints are validated
- AFTER Trigger Execution — Post-event triggers fire with access to the committed changes
- Transaction Completion — All trigger actions are part of the same transaction
Transactional Semantics:
Critical insight: Triggers execute within the transaction context of the triggering statement. This means:
- If any trigger raises an error, the entire statement (and potentially transaction) rolls back
- COMMIT/ROLLBACK in a trigger affects the outer transaction (and is often prohibited)
- Triggers see uncommitted changes made within the same transaction
- Nested triggers see cumulative modifications from prior triggers
Trigger and Constraint Interaction:
A common point of confusion is the relationship between triggers and constraints:
| Timing | What Happens |
|---|---|
| BEFORE triggers | Fire first, can modify the row, cannot assume constraint validity |
| DML execution | Row is written to storage (but not yet committed) |
| Constraint checking | PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK are validated |
| AFTER triggers | Fire after constraints pass, see the validated row |
If a constraint fails, AFTER triggers never fire. If a BEFORE trigger modifies a row to violate a constraint, the statement fails at the constraint-checking phase.
Triggers inherit the transaction isolation level of the executing session. Under READ COMMITTED, triggers may see different committed data from other transactions between their invocation and completion. Under SERIALIZABLE, triggers operate as if exclusive. Design trigger logic with isolation semantics in mind.
Essential Trigger Terminology
Before diving into trigger syntax and patterns, we must establish a precise vocabulary. These terms appear throughout trigger documentation and discussions:
- •Triggering Event — The DML operation (INSERT, UPDATE, DELETE) that causes the trigger to fire. Some systems allow multiple events (e.g.,
INSERT OR UPDATE). - •Activation Time — When the trigger fires relative to the event: BEFORE (pre-modification), AFTER (post-modification), or INSTEAD OF (replaces the operation).
- •Row-Level Trigger — Fires once for each row affected by the DML statement. Has access to OLD and NEW row values.
- •Statement-Level Trigger — Fires once per DML statement, regardless of how many rows are affected. No access to individual row values.
- •OLD Row (Correlation Name) — A pseudo-record representing the row's state before the triggering event. Available for UPDATE and DELETE events.
- •NEW Row (Correlation Name) — A pseudo-record representing the row's state after the triggering event. Available for INSERT and UPDATE events. Can be modified in BEFORE triggers.
- •Trigger Body — The procedural code that executes when the trigger fires. May contain SQL statements, variable declarations, control flow, and error handling.
- •Trigger Condition (WHEN clause) — An optional Boolean expression that must evaluate to TRUE for the trigger body to execute.
| Event | OLD Available? | NEW Available? | Modifiable NEW? |
|---|---|---|---|
| INSERT | No (row didn't exist) | Yes | Yes (BEFORE only) |
| UPDATE | Yes | Yes | Yes (BEFORE only) |
| DELETE | Yes | No (row is being deleted) | N/A |
- •Cascading Triggers — A trigger whose actions cause another trigger to fire. Can lead to complex chains or infinite loops if not carefully designed.
- •Recursive Triggers — A trigger that fires on a table and performs DML on the same table, potentially causing itself to fire again.
- •Trigger Enabling/Disabling — Most DBMS allow temporarily disabling triggers without dropping them (useful for bulk loads).
- •Transition Tables (PostgreSQL) — Named tables containing all OLD rows or all NEW rows affected by the statement, useful for statement-level triggers.
- •Compound Trigger (Oracle) — A single trigger definition with multiple timing sections (BEFORE EACH ROW, AFTER EACH ROW, BEFORE STATEMENT, AFTER STATEMENT).
- •Mutating Table Error — In some systems (notably Oracle), a row-level trigger cannot query or modify the table it's triggered from (the table is 'mutating').
PostgreSQL uses OLD and NEW directly. Oracle uses :OLD and :NEW with colon prefixes. SQL Server provides the inserted and deleted pseudo-tables rather than row correlation names. Always verify syntax for your target DBMS.
The Event-Condition-Action (ECA) Model
Triggers implement the Event-Condition-Action (ECA) paradigm from active database theory. Understanding this model provides insight into trigger design and behavior.
The Three Components:
1. Event: The event is the occurrence that activates the trigger. In standard SQL triggers, events are DML operations:
- INSERT — A new row is added to the table
- UPDATE — One or more rows are modified (optionally restricted to specific columns)
- DELETE — One or more rows are removed
Some systems extend events to include DDL operations (schema changes) or database events (logon, startup/shutdown).
2. Condition: The condition is an optional predicate that further restricts when the trigger body executes. If specified (via a WHEN clause), the trigger body runs only if the condition evaluates to TRUE.
Conditions enable selective firing without cluttering the trigger body with IF statements:
1234567891011
-- Only fire when salary increase exceeds 20%CREATE TRIGGER salary_audit_large_increases AFTER UPDATE OF salary ON employees FOR EACH ROW WHEN (NEW.salary > OLD.salary * 1.20) -- Condition filters firingBEGIN INSERT INTO salary_audit_log (emp_id, old_salary, new_salary, change_pct, changed_at) VALUES (NEW.emp_id, OLD.salary, NEW.salary, (NEW.salary - OLD.salary) / OLD.salary * 100, CURRENT_TIMESTAMP);END;3. Action: The action is the procedural code that executes when the event occurs and the condition (if any) is satisfied. Actions can:
- Perform additional DML (INSERT, UPDATE, DELETE on other tables)
- Call stored procedures and functions
- Raise errors to abort the triggering operation
- Modify the NEW row (in BEFORE triggers)
- Execute external operations (where supported, e.g., sending alerts)
ECA Execution Semantics:
The ECA model specifies not just what triggers do, but when and how:
| Semantic Aspect | Behavior |
|---|---|
| Immediate vs. Deferred | Standard triggers fire immediately; some systems support deferred execution (at transaction commit) |
| Coupling Mode | Triggers are coupled to the triggering transaction; their success/failure affects the transaction |
| Priority/Ordering | When multiple triggers exist for the same event, execution order may be defined or undefined |
| Net Effect | Some systems coalesce multiple modifications to the same row into a single trigger invocation |
Well-designed triggers have precise conditions that limit firing to necessary cases, and focused actions that do one thing well. Avoid triggers that fire broadly and then use complex IF-ELSE logic inside the body—this creates maintenance nightmares and performance issues.
When to Use (and Not Use) Triggers
Triggers are powerful but not universally appropriate. Knowing when triggers are the right tool—and when they're a liability—is essential for sound database design.
- •Audit Logging — Tracking who changed what, when, with before/after values
- •Derived Data Maintenance — Keeping denormalized columns or summary tables in sync
- •Complex Business Rules — Validations that can't be expressed as constraints
- •Referential Actions — Custom cascade behaviors beyond standard ON DELETE/UPDATE
- •Data Archiving — Automatically moving deleted rows to archive tables
- •Cross-Table Integrity — Ensuring consistency across related tables
- •Notification/Alerting — Signaling external systems of data changes
- •Simple Constraints — Use CHECK, UNIQUE, FOREIGN KEY instead
- •Default Values — Use column defaults, not BEFORE INSERT triggers
- •Application Logic — Business logic that belongs in the application tier
- •Performance-Critical Paths — Triggers add overhead to every affected DML
- •Debugging-Unfriendly Operations — Invisible side effects complicate troubleshooting
- •Cross-Database Operations — Triggers can't reliably span databases/connections
- •High-Frequency Bulk Operations — Triggers on bulk load tables can devastate performance
Triggers are 'invisible' to developers querying or modifying data. A simple INSERT statement may cascade through multiple triggers, modifying many tables, raising errors from unexpected places, or causing performance degradation. Document triggers thoroughly and use them sparingly.
Decision Framework:
Ask these questions before implementing a trigger:
- Can this be done with a constraint? If yes, use the constraint.
- Does this logic belong in the database layer? If it's purely presentation or application logic, it shouldn't be here.
- Will this fire frequently on hot tables? Consider performance impact.
- Can developers understand the system with triggers in place? If triggers make the system opaque, reconsider.
- What happens if the trigger fails? Is rolling back the entire transaction acceptable?
- Is this truly data-centric? Triggers are for data integrity and data-driven side effects, not general computation.
Triggers vs. Alternatives
Triggers exist within an ecosystem of mechanisms for ensuring data integrity and implementing reactive behavior. Understanding alternatives helps you choose the right tool.
| Mechanism | Best For | Advantages | Drawbacks |
|---|---|---|---|
| CHECK Constraints | Column-level validation | Declarative, optimized, always enforced | Limited to single-row, single-table logic |
| FOREIGN KEY Constraints | Referential integrity | Declarative, with CASCADE/SET NULL options | Only standard referential actions |
| DEFAULT Values | Providing missing values | Simple, efficient, transparent | Only constant or simple expressions |
| Application Layer Logic | Complex business rules | Full language power, testable, debuggable | Not enforced for all data access paths |
| Stored Procedures | Encapsulated operations | Explicit invocation, full control | Requires discipline to always use |
| Materialized Views | Derived data | Automatic refresh options | Refresh timing, storage overhead |
| Change Data Capture (CDC) | Event streaming, auditing | Decoupled, async, doesn't impact write path | Additional infrastructure, latency |
| Triggers | Cross-cutting data reactions | Automatic, unavoidable, transactional | Hidden, can cause cascades, debugging hard |
Architectural Perspective:
In modern systems, there's often tension between database-resident logic (triggers, stored procedures) and application-resident logic (ORM hooks, event systems). The trend has been toward 'thicker' applications and 'thinner' databases—but this introduces risks:
| Approach | Risk |
|---|---|
| Logic in application | Multiple apps, scripts, or direct SQL can bypass rules |
| Logic in database | Coupled to one database vendor, harder to test/debug |
Triggers are the strongest guarantee that rules apply universally—but at the cost of vendor lock-in and reduced transparency.
The Pragmatic Middle Ground:
- Use constraints for all rules they can express
- Use triggers for unavoidable data-layer reactions (audit, archive, cross-table sync)
- Use application logic for business rules that need flexibility, testing, and visibility
- Use CDC for external notifications and async processing
Some organizations implement critical rules in both triggers and application code. The application performs validation for immediate user feedback; the trigger acts as a safety net. This redundancy has maintenance costs but provides defense-in-depth for critical integrity requirements.
KEEP LEARNING
There’s more to understand.
You’re reading the introduction. Create a free account to read the complete lesson, save notes and keep track of your progress.