Transactions & Programmability
Triggers
A trigger is a stored program that automatically executes in response to specific events on a table or view.
Overview
A trigger is a stored program that automatically executes in response to specific events on a table or view. The SQL standard introduced triggers in SQL-99, defining them as event-condition-action rules. Triggers can fire before or after INSERT, UPDATE, or DELETE statements, and can be row-level (once per affected row) or statement-level (once per statement). They are used to enforce complex integrity constraints, audit changes, maintain derived data, and implement business rules that cannot be expressed with declarative constraints. Unlike stored procedures, which are invoked explicitly, triggers run implicitly as part of the data modification statement. This implicit execution makes them powerful but also potentially hazardous, as they can introduce hidden side effects. Vendor implementations vary: PostgreSQL supports row-level and statement-level triggers with rich procedural languages; MySQL supports row-level triggers only; SQL Server supports both AFTER and INSTEAD OF triggers; Oracle supports BEFORE, AFTER, and INSTEAD OF triggers. The standard provides a foundation, but exact syntax and capabilities differ across systems.
Syntax and usage
The SQL standard defines a CREATE TRIGGER statement with the general form: CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name [REFERENCING ...] [FOR EACH {ROW | STATEMENT}] [WHEN (condition)] EXECUTE PROCEDURE procedure_name;. The trigger body is typically a stored procedure or a block of procedural code. Many systems extend this with proprietary syntax. For example, PostgreSQL uses CREATE TRIGGER ... EXECUTE FUNCTION function_name(); and allows INSTEAD OF on views. MySQL uses CREATE TRIGGER ... FOR EACH ROW BEGIN ... END; with inline procedural statements. SQL Server uses CREATE TRIGGER ... ON table_name AFTER INSERT AS BEGIN ... END; and supports INSTEAD OF. Oracle uses CREATE OR REPLACE TRIGGER ... BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END;. Triggers are dropped with DROP TRIGGER trigger_name;. The standard also defines trigger events, timing (BEFORE, AFTER), and granularity (ROW, STATEMENT). The WHEN clause provides a condition that must evaluate to true for the trigger action to execute. Referencing clauses allow access to old and new row values, often via correlation names like OLD and NEW.
How it works
When a data modification statement (INSERT, UPDATE, DELETE) is executed, the database engine identifies any triggers defined on the target table that match the event and timing. For a BEFORE trigger, the trigger action runs before the modification is applied; it can validate or modify the new row values. For an AFTER trigger, the action runs after the modification, and the changes are visible to the trigger. Row-level triggers execute once for each row affected by the statement, while statement-level triggers execute once for the entire statement. The trigger action can access transition variables: OLD values (for UPDATE and DELETE) and NEW values (for INSERT and UPDATE). In the SQL standard, these are exposed through a REFERENCING clause, but many systems use implicit OLD and NEW correlation names. Triggers execute within the same transaction as the triggering statement. If a trigger raises an error or executes a rollback, the entire transaction may be rolled back. Triggers can also cause cascading effects if they perform additional data modifications that fire other triggers. The order of trigger execution is often unspecified unless the system provides a way to set precedence. Recursive or nested triggers are possible but may be restricted by configuration.
Examples
Consider an employees table and an audit log table. A row-level AFTER INSERT trigger can record new hires. In standard SQL: CREATE TRIGGER log_new_hire AFTER INSERT ON employees REFERENCING NEW AS new_row FOR EACH ROW INSERT INTO audit_log (employee_id, action, changed_at) VALUES (new_row.id, 'INSERT', CURRENT_TIMESTAMP);. In PostgreSQL, the same logic uses a trigger function: CREATE FUNCTION log_new_hire() RETURNS trigger AS $$ BEGIN INSERT INTO audit_log (employee_id, action, changed_at) VALUES (NEW.id, 'INSERT', CURRENT_TIMESTAMP); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER log_new_hire AFTER INSERT ON employees FOR EACH ROW EXECUTE FUNCTION log_new_hire();. In MySQL: CREATE TRIGGER log_new_hire AFTER INSERT ON employees FOR EACH ROW INSERT INTO audit_log (employee_id, action, changed_at) VALUES (NEW.id, 'INSERT', NOW());. A BEFORE UPDATE trigger can enforce a rule, e.g., prevent salary decreases: CREATE TRIGGER check_salary BEFORE UPDATE ON employees FOR EACH ROW BEGIN IF NEW.salary < OLD.salary THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot decrease'; END IF; END;. These examples show how triggers automate auditing and validation.
Common pitfalls
Triggers can introduce hidden complexity and unexpected behavior. Because they fire implicitly, developers may forget they exist, leading to side effects that are hard to debug. Performance can degrade if triggers perform expensive operations for each row, especially in bulk updates. Row-level triggers on large data modifications can cause significant overhead. Triggers can also cause cascading or recursive firing, potentially leading to infinite loops or exceeding recursion limits. Transaction management becomes tricky: a trigger that fails can abort the entire transaction, and error handling may be non-obvious. In some systems, triggers cannot be used on temporary tables or views, or have restrictions on the statements they can execute. Portability is limited because trigger syntax and capabilities vary widely across vendors. The SQL standard's trigger model is not fully implemented by all databases, and some systems lack statement-level triggers or BEFORE triggers. Additionally, triggers can obscure business logic, making it harder to understand the full effect of a data modification. Testing triggers requires careful setup and teardown of test data. Finally, triggers may interact poorly with replication or bulk loading tools that bypass trigger execution.
Best practices
Use triggers sparingly and only when declarative constraints or application logic cannot achieve the goal. Document all triggers thoroughly, including their purpose, timing, and expected side effects. Keep trigger logic simple and fast; avoid complex queries or external calls. Prefer statement-level triggers over row-level when possible to reduce overhead. Ensure triggers are idempotent and do not rely on external state. Use triggers for auditing, logging, and enforcing complex business rules that must be applied consistently regardless of the application. Avoid using triggers to maintain derived data that could be handled by materialized views or scheduled jobs. When multiple triggers exist on a table, define a clear execution order if the system allows it. Always consider transaction boundaries: triggers should not commit or rollback independently. Test triggers with representative data volumes to assess performance impact. Provide a way to disable triggers for bulk operations, such as ALTER TABLE ... DISABLE TRIGGER where supported. Finally, be aware of vendor-specific limitations and document them for portability.
Frequently asked questions
Q: Can a trigger be rolled back independently of the triggering statement? A: No, triggers execute within the same transaction as the triggering statement. If the trigger fails, the entire transaction can be rolled back. Q: What is the difference between a row-level and a statement-level trigger? A: A row-level trigger fires once for each row affected by the statement, while a statement-level trigger fires once for the entire statement, regardless of the number of rows. Q: Are triggers supported in all SQL databases? A: Most relational databases support triggers, but syntax and features vary. The SQL standard defines triggers, but not all vendors implement the full standard. For example, MySQL only supports row-level triggers, while PostgreSQL supports both row-level and statement-level triggers.