Loading learning content...
Consider this statement:
UPDATE employees SET salary = salary * 1.05 WHERE department_id = 10;
If 50 employees are in department 10, should your audit trigger fire:
Both patterns are legitimate depending on your requirements. Row-level triggers (FOR EACH ROW) fire once per affected row, with access to individual OLD and NEW values. Statement-level triggers (FOR EACH STATEMENT) fire once per DML statement, regardless of how many rows are affected.
This distinction profoundly affects:
By the end of this page, you will understand the precise semantics of row-level and statement-level triggers, their respective strengths and limitations, how to access set data in statement-level triggers using transition tables, and when to choose each granularity.
Row-level triggers execute once for each row affected by the triggering statement. They receive individual row data through OLD and NEW correlation names (or equivalent), enabling fine-grained operations on each row.
Key Characteristics:
| Aspect | Row-Level Trigger Behavior |
|---|---|
| Execution frequency | Once per affected row |
| OLD/NEW access | Direct access to single row's before/after values |
| Can modify NEW | Yes (in BEFORE triggers) |
| Performance | Linear with row count (N rows = N executions) |
| Statement context | Limited awareness of other rows in same statement |
| Support | Universal (all major DBMS) |
Syntax Across DBMS:
12345678910111213141516171819202122232425
-- PostgreSQL: Row-level triggerCREATE TRIGGER trg_example AFTER UPDATE ON employees FOR EACH ROW -- Explicit row-level EXECUTE FUNCTION example_function(); -- Oracle: Row-level triggerCREATE TRIGGER trg_example AFTER UPDATE ON employees FOR EACH ROW -- Explicit row-levelBEGIN -- Trigger bodyEND;/ -- MySQL: Row-level trigger (only option)CREATE TRIGGER trg_example AFTER UPDATE ON employees FOR EACH ROW -- Only row-level supportedBEGIN -- Trigger bodyEND; -- SQL Server: Statement-level only - no row-level syntax-- (but processes all rows via inserted/deleted tables)When Row-Level Triggers Excel:
Individual Row Validation Each row can be validated and rejected independently. If row 3 of 100 fails validation in a BEFORE trigger, rows 1-2 still fail (rolled back), but the error indicates specifically which row was problematic.
Row-Specific Transformations Modifying NEW values based on the specific row's data—e.g., computing a row's derived column based on that row's other columns.
Per-Row Audit Logging Creating an audit record for each individual change, preserving the complete OLD and NEW state for each row.
Referential Logic Validating each row against related data—e.g., ensuring each employee's salary falls within their department's range.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263
-- Row-Level Trigger Example: Per-Row Processing-- PostgreSQL syntax CREATE OR REPLACE FUNCTION process_employee_row()RETURNS TRIGGER AS $$DECLARE v_dept_min_salary DECIMAL(10,2); v_dept_max_salary DECIMAL(10,2);BEGIN -- Get department salary range SELECT min_salary, max_salary INTO v_dept_min_salary, v_dept_max_salary FROM departments WHERE dept_id = NEW.dept_id; -- Validate this specific row's salary IF NEW.salary < v_dept_min_salary THEN RAISE EXCEPTION 'Salary % for employee % is below department minimum %', NEW.salary, NEW.employee_name, v_dept_min_salary; END IF; IF NEW.salary > v_dept_max_salary THEN RAISE EXCEPTION 'Salary % for employee % exceeds department maximum %', NEW.salary, NEW.employee_name, v_dept_max_salary; END IF; -- Set row-specific computed values NEW.salary_grade := CASE WHEN NEW.salary < v_dept_min_salary + (v_dept_max_salary - v_dept_min_salary) * 0.33 THEN 'Junior' WHEN NEW.salary < v_dept_min_salary + (v_dept_max_salary - v_dept_min_salary) * 0.66 THEN 'Mid' ELSE 'Senior' END; -- Audit this specific row change IF TG_OP = 'UPDATE' THEN INSERT INTO employee_changes ( employee_id, field_changed, old_value, new_value, changed_at ) SELECT NEW.employee_id, 'salary', OLD.salary::TEXT, NEW.salary::TEXT, CURRENT_TIMESTAMP WHERE OLD.salary IS DISTINCT FROM NEW.salary; END IF; RETURN NEW;END;$$ LANGUAGE plpgsql; CREATE TRIGGER trg_process_employee BEFORE INSERT OR UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION process_employee_row(); -- When this runs for 100 employees:-- UPDATE employees SET salary = salary * 1.05 WHERE department_id = 10;-- The trigger function executes 100 times, once per employee rowRow-level triggers add overhead proportional to the number of affected rows. A trigger that takes 10ms per row becomes a 10-second delay for 1,000 rows. For bulk operations, this compounding can be prohibitive. Always benchmark triggers against realistic data volumes.
Statement-level triggers execute exactly once per DML statement, regardless of whether zero, one, or millions of rows are affected. They provide a single execution point for aggregate or summary operations.
Key Characteristics:
| Aspect | Statement-Level Trigger Behavior |
|---|---|
| Execution frequency | Once per statement (regardless of row count) |
| OLD/NEW access | Not directly available (use transition tables) |
| Can modify rows | Not directly (rows already processed) |
| Performance | Constant (O(1)) regardless of row count |
| Statement context | Full awareness of all affected rows (via transition tables) |
| Support | PostgreSQL, Oracle, DB2; NOT MySQL; SQL Server is inherently statement-level |
Important: Statement-level triggers fire even if zero rows are affected. An UPDATE WHERE clause that matches no rows still fires BEFORE/AFTER STATEMENT triggers.
1234567891011121314151617181920212223242526
-- PostgreSQL: Statement-level triggerCREATE TRIGGER trg_example AFTER UPDATE ON employees FOR EACH STATEMENT -- Fires once, not per row EXECUTE FUNCTION example_function(); -- Oracle: Statement-level trigger (default when FOR EACH ROW is omitted)CREATE TRIGGER trg_example AFTER UPDATE ON employees -- No FOR EACH ROW = statement-levelBEGIN -- Trigger bodyEND;/ -- MySQL: NOT SUPPORTED-- MySQL only supports row-level triggers -- SQL Server: Inherently statement-level-- All SQL Server triggers are statement-level (use inserted/deleted tables)CREATE TRIGGER trg_example ON employeesAFTER UPDATEASBEGIN -- Always processes all affected rows via inserted/deletedEND;When Statement-Level Triggers Excel:
Aggregate Validations Validating constraints across all affected rows together—e.g., "total salary increase for this department cannot exceed $100,000".
Summary Logging Creating a single audit record that summarizes the operation—"50 employees received a 5% raise" rather than 50 individual log entries.
Batch Notifications Sending a single notification after a batch operation rather than 100 separate notifications.
Statistics/Counter Updates Updating aggregated counts or totals once, rather than incrementing for each row.
Global State Changes Operations that should happen once regardless of how many rows changed—like invalidating a cache.
1234567891011121314151617181920212223242526272829303132333435363738394041
-- Statement-Level Trigger Example: Aggregate Operations-- PostgreSQL syntax (without transition tables) CREATE OR REPLACE FUNCTION log_salary_update_summary()RETURNS TRIGGER AS $$DECLARE v_affected_count INTEGER; v_dept_id INTEGER;BEGIN -- Note: In statement-level triggers without transition tables, -- we don't have direct access to which rows were affected. -- We can only know that an UPDATE happened on the table. -- This is a limitation - we can log that something happened, -- but not the specifics without transition tables. INSERT INTO operation_log ( table_name, operation_type, operation_time, executed_by, description ) VALUES ( TG_TABLE_NAME, TG_OP, CURRENT_TIMESTAMP, CURRENT_USER, 'Salary update operation completed' ); RETURN NULL; -- Statement-level triggers return NULLEND;$$ LANGUAGE plpgsql; CREATE TRIGGER trg_log_salary_update AFTER UPDATE OF salary ON employees FOR EACH STATEMENT EXECUTE FUNCTION log_salary_update_summary(); -- This trigger fires exactly once, whether the UPDATE affects-- 0 rows, 1 row, or 10,000 rowsStatement-level triggers fire even when no rows are affected. UPDATE employees SET salary = 0 WHERE 1 = 0 still fires the AFTER STATEMENT trigger. This is useful for logging all operations, but be aware of it when your logic assumes rows were affected.
The main limitation of statement-level triggers—inability to see individual row data—is addressed by transition tables (also called referencing tables). Transition tables provide access to all affected rows as a set, enabling powerful aggregate operations.
Transition Table Concept:
These are temporary, read-only tables that exist only during trigger execution and contain the complete set of affected rows.
Support Matrix:
| DBMS | Transition Table Support |
|---|---|
| PostgreSQL | Full support (REFERENCING OLD TABLE AS, NEW TABLE AS) |
| Oracle | Compound triggers provide similar capability |
| SQL Server | inserted and deleted tables (always available, inherently statement-level) |
| DB2 | Full support (OLD_TABLE, NEW_TABLE) |
| MySQL | Not supported (no statement-level triggers) |
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879
-- PostgreSQL: Transition Tables in Statement-Level Triggers-- Available since PostgreSQL 10 CREATE OR REPLACE FUNCTION process_batch_salary_update()RETURNS TRIGGER AS $$DECLARE v_total_increase DECIMAL(12,2); v_employee_count INTEGER; v_max_increase DECIMAL(12,2); v_avg_increase DECIMAL(10,2);BEGIN -- Access all affected rows via transition tables -- old_employees and new_employees are the transition table names -- Calculate aggregate statistics across all updated rows SELECT SUM(n.salary - o.salary), COUNT(*), MAX(n.salary - o.salary), AVG(n.salary - o.salary) INTO v_total_increase, v_employee_count, v_max_increase, v_avg_increase FROM new_employees n JOIN old_employees o ON n.employee_id = o.employee_id; -- Validation: Total increase cannot exceed budget IF v_total_increase > 100000 THEN RAISE EXCEPTION 'Total salary increase of % exceeds budget limit of $100,000', v_total_increase; END IF; -- Single summary audit log entry INSERT INTO salary_batch_log ( operation_time, employees_affected, total_increase, max_individual_increase, avg_increase, executed_by ) VALUES ( CURRENT_TIMESTAMP, v_employee_count, v_total_increase, v_max_increase, v_avg_increase, CURRENT_USER ); -- Identify outliers (increases > 20%) INSERT INTO salary_outlier_review ( employee_id, old_salary, new_salary, increase_pct, flagged_at ) SELECT n.employee_id, o.salary, n.salary, ((n.salary - o.salary) / o.salary * 100)::DECIMAL(5,2), CURRENT_TIMESTAMP FROM new_employees n JOIN old_employees o ON n.employee_id = o.employee_id WHERE (n.salary - o.salary) / o.salary > 0.20; RETURN NULL;END;$$ LANGUAGE plpgsql; -- Create trigger with transition table referencesCREATE TRIGGER trg_batch_salary_update AFTER UPDATE OF salary ON employees REFERENCING OLD TABLE AS old_employees NEW TABLE AS new_employees FOR EACH STATEMENT EXECUTE FUNCTION process_batch_salary_update();Transition tables enable set-based operations that are far more efficient than row-by-row processing. Instead of 1000 INSERT statements (one per row in a row-level trigger), you can do one INSERT...SELECT from the transition table. This is a massive performance win for bulk operations.
123456789101112131415161718192021222324252627282930
-- Transition Table Availability by Event Type -- INSERT: Only NEW TABLE availableCREATE TRIGGER trg_after_insert AFTER INSERT ON employees REFERENCING NEW TABLE AS inserted_rows FOR EACH STATEMENT EXECUTE FUNCTION handle_inserts(); -- UPDATE: Both OLD TABLE and NEW TABLE availableCREATE TRIGGER trg_after_update AFTER UPDATE ON employees REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows FOR EACH STATEMENT EXECUTE FUNCTION handle_updates(); -- DELETE: Only OLD TABLE availableCREATE TRIGGER trg_after_delete AFTER DELETE ON employees REFERENCING OLD TABLE AS deleted_rows FOR EACH STATEMENT EXECUTE FUNCTION handle_deletes(); -- Common Error: Referencing unavailable transition table-- This will error:-- CREATE TRIGGER trg_error-- AFTER INSERT ON employees-- REFERENCING OLD TABLE AS old_rows -- ERROR: OLD TABLE not available for INSERT-- FOR EACH STATEMENT-- EXECUTE FUNCTION some_function();Oracle provides compound triggers—a single trigger with multiple timing sections. This is particularly valuable for implementing patterns that require both row-level data collection and statement-level summary processing.
Compound Trigger Structure:
A compound trigger can have up to four timing sections:
The Mutating Table Problem Solution:
In Oracle, a row-level trigger cannot query or modify the table it's triggered from—the table is "mutating." Compound triggers solve this by:
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687
-- Oracle Compound Trigger ExampleCREATE OR REPLACE TRIGGER employee_salary_compound_trg FOR UPDATE OF salary ON employees COMPOUND TRIGGER -- Declaration section (shared across all timing points) TYPE t_emp_record IS RECORD ( employee_id employees.employee_id%TYPE, old_salary employees.salary%TYPE, new_salary employees.salary%TYPE ); TYPE t_emp_table IS TABLE OF t_emp_record INDEX BY PLS_INTEGER; g_emp_changes t_emp_table; g_index PLS_INTEGER := 0; g_total_increase NUMBER := 0; -- BEFORE STATEMENT: Initialize BEFORE STATEMENT IS BEGIN g_emp_changes.DELETE; g_index := 0; g_total_increase := 0; END BEFORE STATEMENT; -- BEFORE EACH ROW: Validate individual rows BEFORE EACH ROW IS BEGIN -- Per-row validation IF :NEW.salary < 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative'); END IF; END BEFORE EACH ROW; -- AFTER EACH ROW: Collect row data AFTER EACH ROW IS BEGIN g_index := g_index + 1; g_emp_changes(g_index).employee_id := :NEW.employee_id; g_emp_changes(g_index).old_salary := :OLD.salary; g_emp_changes(g_index).new_salary := :NEW.salary; g_total_increase := g_total_increase + (:NEW.salary - :OLD.salary); END AFTER EACH ROW; -- AFTER STATEMENT: Process collected data AFTER STATEMENT IS BEGIN -- Now we can safely query the employees table -- (it's no longer mutating) -- Aggregate validation IF g_total_increase > 100000 THEN RAISE_APPLICATION_ERROR(-20002, 'Total salary increase exceeds $100,000 limit. Total: $' || g_total_increase); END IF; -- Batch insert to audit log FORALL i IN 1..g_emp_changes.COUNT INSERT INTO salary_audit ( employee_id, old_salary, new_salary, change_time ) VALUES ( g_emp_changes(i).employee_id, g_emp_changes(i).old_salary, g_emp_changes(i).new_salary, SYSTIMESTAMP ); -- Log summary INSERT INTO batch_update_log ( table_name, records_affected, total_increase, executed_at ) VALUES ( 'EMPLOYEES', g_index, g_total_increase, SYSTIMESTAMP ); END AFTER STATEMENT; END employee_salary_compound_trg;/The FORALL statement in Oracle performs bulk DML, sending arrays to the database in a single round trip. Combined with compound triggers, this enables efficient batch processing while still having access to individual row data collected during row-level execution.
Understanding performance characteristics helps you choose the right granularity:
Execution Count:
| Rows Affected | Row-Level Executions | Statement-Level Executions |
|---|---|---|
| 0 | 0 | 1 |
| 1 | 1 | 1 |
| 100 | 100 | 1 |
| 10,000 | 10,000 | 1 |
| 1,000,000 | 1,000,000 | 1 |
Performance Implications:
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758
-- Performance Demonstration-- PostgreSQL syntax -- Scenario: Log changes to salary_audit table -- ROW-LEVEL APPROACHCREATE OR REPLACE FUNCTION row_level_audit()RETURNS TRIGGER AS $$BEGIN INSERT INTO salary_audit (emp_id, old_sal, new_sal, changed_at) VALUES (NEW.employee_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP); RETURN NEW;END;$$ LANGUAGE plpgsql; CREATE TRIGGER trg_row_audit AFTER UPDATE OF salary ON employees FOR EACH ROW EXECUTE FUNCTION row_level_audit(); -- For UPDATE affecting 10,000 rows:-- - 10,000 trigger executions-- - 10,000 INSERT statements-- - 10,000 lock acquisitions on salary_audit-- Typical time: ~2-5 seconds ------------------------------------------------- -- STATEMENT-LEVEL APPROACH with transition tablesCREATE OR REPLACE FUNCTION statement_level_audit()RETURNS TRIGGER AS $$BEGIN INSERT INTO salary_audit (emp_id, old_sal, new_sal, changed_at) SELECT n.employee_id, o.salary, n.salary, CURRENT_TIMESTAMP FROM new_employees n JOIN old_employees o ON n.employee_id = o.employee_id; RETURN NULL;END;$$ LANGUAGE plpgsql; CREATE TRIGGER trg_statement_audit AFTER UPDATE OF salary ON employees REFERENCING OLD TABLE AS old_employees NEW TABLE AS new_employees FOR EACH STATEMENT EXECUTE FUNCTION statement_level_audit(); -- For UPDATE affecting 10,000 rows:-- - 1 trigger execution-- - 1 INSERT...SELECT statement (bulk operation)-- - Bulk lock handling-- Typical time: ~100-300 milliseconds -- Note: The statement-level approach can be 10-50x faster for bulk operationsFor complex requirements, combine both: use row-level triggers for per-row validation and modification, and statement-level triggers for batch auditing and notifications. Oracle's compound triggers make this natural; in PostgreSQL, create separate triggers at each granularity.
Use this decision framework to select the appropriate trigger granularity:
| Requirement | Recommended | Rationale |
|---|---|---|
| Modify incoming column values | Row-Level BEFORE | Only row-level can modify NEW |
| Per-row validation | Row-Level BEFORE | Reject individual invalid rows |
| Compute row-specific derived values | Row-Level BEFORE | Access to specific row's data |
| Access auto-generated IDs (INSERT) | Row-Level AFTER | ID available after row insert |
| Create related records per row | Row-Level AFTER | Need individual row's FK values |
| Aggregate validation (total budget) | Statement-Level | Need sum/count across all rows |
| Batch audit logging | Statement-Level + Transition Tables | One log entry for batch |
| Cache invalidation | Statement-Level | Invalidate once, not per row |
| External notification | Statement-Level | One notification per operation |
| Statistics update (increment count) | Statement-Level | COUNT(*) of affected rows |
MySQL only supports row-level triggers. For statement-level patterns, you must either: (1) use a stored procedure that wraps the DML and performs summary logic, or (2) accumulate data in a temporary table during row-level triggers and process after the statement (complex and error-prone).
Real-world scenarios often benefit from combining row-level and statement-level triggers:
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192
-- Combined Pattern: Row Validation + Statement Summary-- PostgreSQL syntax -- 1. Row-level trigger for per-row validation and modificationCREATE OR REPLACE FUNCTION validate_and_transform_employee()RETURNS TRIGGER AS $$BEGIN -- Per-row validation IF NEW.salary < 0 THEN RAISE EXCEPTION 'Invalid salary for employee %: %', NEW.employee_id, NEW.salary; END IF; -- Per-row transformation NEW.email := LOWER(TRIM(NEW.email)); NEW.updated_at := CURRENT_TIMESTAMP; -- Per-row derived value NEW.salary_band := CASE WHEN NEW.salary < 50000 THEN 'Band-1' WHEN NEW.salary < 100000 THEN 'Band-2' ELSE 'Band-3' END; RETURN NEW;END;$$ LANGUAGE plpgsql; CREATE TRIGGER trg_01_validate_employee BEFORE INSERT OR UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION validate_and_transform_employee(); -- 2. Statement-level trigger for batch summaryCREATE OR REPLACE FUNCTION summarize_employee_changes()RETURNS TRIGGER AS $$DECLARE v_insert_count INTEGER := 0; v_update_count INTEGER := 0; v_salary_change DECIMAL(12,2) := 0;BEGIN -- Count inserts and updates from transition tables IF TG_OP = 'INSERT' THEN SELECT COUNT(*) INTO v_insert_count FROM new_employees; END IF; IF TG_OP = 'UPDATE' THEN SELECT COUNT(*), SUM(n.salary - o.salary) INTO v_update_count, v_salary_change FROM new_employees n JOIN old_employees o ON n.employee_id = o.employee_id; END IF; -- Single summary log entry INSERT INTO employee_change_summary ( operation, records_inserted, records_updated, total_salary_change, operation_time, executed_by ) VALUES ( TG_OP, v_insert_count, v_update_count, v_salary_change, CURRENT_TIMESTAMP, CURRENT_USER ); -- Send single notification PERFORM pg_notify('employee_changes', JSON_BUILD_OBJECT( 'operation', TG_OP, 'count', COALESCE(v_insert_count, 0) + COALESCE(v_update_count, 0), 'salary_change', v_salary_change )::TEXT); RETURN NULL;END;$$ LANGUAGE plpgsql; CREATE TRIGGER trg_02_summarize_inserts AFTER INSERT ON employees REFERENCING NEW TABLE AS new_employees FOR EACH STATEMENT EXECUTE FUNCTION summarize_employee_changes(); CREATE TRIGGER trg_02_summarize_updates AFTER UPDATE ON employees REFERENCING OLD TABLE AS old_employees NEW TABLE AS new_employees FOR EACH STATEMENT EXECUTE FUNCTION summarize_employee_changes();Prefix trigger names with numbers (01_, 02_) to control execution order in PostgreSQL. Row-level BEFORE triggers should run first (01_), then row-level AFTER can run if needed, then statement-level triggers (02_) run after all row processing completes.
Trigger granularity determines how often your trigger fires and what data it can access. The right choice depends on your specific requirements:
What's Next:
With granularity understood, the final page explores trigger use cases—real-world patterns for audit logging, referential integrity, business rules, and more, bringing together all the concepts we've covered.
You now understand row-level and statement-level triggers, transition tables, performance implications, and how to choose the right granularity. This knowledge enables you to design optimal trigger strategies for any requirement.