A special type of stored procedure. Automatically executed in response to modifications on a particular table or view. Commonly used to enforce business rules, maintain audit trails, or automatically update related data.
Can be set to fire BEFORE or AFTER events: INSERT, UPDATE, or DELETE.
OLD and NEW
Inside a trigger body, 2 special row references are available:
OLD: the row as it was before the operation (available inUPDATEandDELETE)NEW: the row as it will be after the operation (available inINSERTandUPDATE)
AFTER Trigger
Fires after the row is modified. Used for audit logging and cascading updates.
CREATE TRIGGER log_update
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO audit_log(employee_id, action, changed_at)
VALUES (NEW.id, 'UPDATE', NOW());BEFORE Trigger
Fires before the row is modified. Used to validate or transform data before it is written.
CREATE TRIGGER normalize_email
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.email = LOWER(NEW.email);Can be modified to fire only on changes to specific columns using OF column_name.
When Not to Use
Modern DBMS provide built-in support for replication and materialized views. Triggers previously used for these purposes add unnecessary complexity.
Triggers cause unintended invocations that are invisible to the application layer and can degrade performance or create hard-to-trace bugs.