SQL - Triggers with Examples
Triggers execute automatically in response to data modification events. Unlike stored procedures that require explicit invocation, triggers fire implicitly when specific DML operations occur. This...
Key Insights
- Triggers are database objects that automatically execute specified actions when INSERT, UPDATE, or DELETE operations occur on a table, enabling automated data validation, auditing, and complex business logic enforcement at the database level.
- The timing (BEFORE/AFTER) and granularity (row-level/statement-level) of triggers fundamentally affect their capabilities and performance characteristics, with BEFORE triggers allowing data modification and AFTER triggers suited for logging and cascading operations.
- Proper trigger design requires careful consideration of recursion, mutating table errors, and performance impact, as poorly implemented triggers can create maintenance nightmares and significant bottlenecks in high-transaction environments.
Understanding Trigger Fundamentals
Triggers execute automatically in response to data modification events. Unlike stored procedures that require explicit invocation, triggers fire implicitly when specific DML operations occur. This makes them powerful for enforcing business rules that must always apply, regardless of which application or user modifies the data.
The basic syntax structure across major databases follows this pattern:
CREATE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
ON table_name
[FOR EACH ROW]
BEGIN
-- trigger logic
END;
The key components are the timing (when the trigger fires), the event (what operation triggers it), and the granularity (row-level vs statement-level).
Audit Trail Implementation
One of the most common use cases for triggers is maintaining audit logs. Here’s a comprehensive example that tracks all changes to an employee table:
-- Create the main table
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(10,2),
department_id INT,
modified_date TIMESTAMP
);
-- Create audit table
CREATE TABLE employees_audit (
audit_id INT PRIMARY KEY AUTO_INCREMENT,
employee_id INT,
operation VARCHAR(10),
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
old_department INT,
new_department INT,
changed_by VARCHAR(50),
changed_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Trigger for INSERT operations
CREATE TRIGGER trg_employees_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO employees_audit (
employee_id, operation, new_salary, new_department, changed_by
) VALUES (
NEW.employee_id, 'INSERT', NEW.salary, NEW.department_id, USER()
);
END;
-- Trigger for UPDATE operations
CREATE TRIGGER trg_employees_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employees_audit (
employee_id, operation,
old_salary, new_salary,
old_department, new_department,
changed_by
) VALUES (
NEW.employee_id, 'UPDATE',
OLD.salary, NEW.salary,
OLD.department_id, NEW.department_id,
USER()
);
END;
-- Trigger for DELETE operations
CREATE TRIGGER trg_employees_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employees_audit (
employee_id, operation, old_salary, old_department, changed_by
) VALUES (
OLD.employee_id, 'DELETE', OLD.salary, OLD.department_id, USER()
);
END;
The NEW and OLD keywords reference the new and old row values respectively. NEW is available in INSERT and UPDATE triggers, while OLD is available in UPDATE and DELETE triggers.
Data Validation with BEFORE Triggers
BEFORE triggers can modify data before it’s written to the table or prevent the operation entirely by raising an error. This is ideal for enforcing complex business rules:
-- Prevent salary decreases and enforce business rules
CREATE TRIGGER trg_validate_salary
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
-- Prevent salary decreases
IF NEW.salary < OLD.salary THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary cannot be decreased. Use compensation adjustment process.';
END IF;
-- Enforce maximum salary increase of 20%
IF NEW.salary > OLD.salary * 1.20 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary increase cannot exceed 20%. Requires executive approval.';
END IF;
-- Auto-update modified timestamp
SET NEW.modified_date = CURRENT_TIMESTAMP;
-- Normalize name formatting
SET NEW.first_name = CONCAT(UPPER(SUBSTRING(NEW.first_name, 1, 1)),
LOWER(SUBSTRING(NEW.first_name, 2)));
SET NEW.last_name = CONCAT(UPPER(SUBSTRING(NEW.last_name, 1, 1)),
LOWER(SUBSTRING(NEW.last_name, 2)));
END;
Cascading Operations and Referential Actions
Triggers can implement complex cascading logic beyond standard foreign key constraints:
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100),
budget DECIMAL(12,2),
total_employees INT DEFAULT 0
);
-- Automatically update department statistics
CREATE TRIGGER trg_dept_employee_count_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
UPDATE departments
SET total_employees = total_employees + 1
WHERE department_id = NEW.department_id;
END;
CREATE TRIGGER trg_dept_employee_count_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
UPDATE departments
SET total_employees = total_employees - 1
WHERE department_id = OLD.department_id;
END;
CREATE TRIGGER trg_dept_employee_count_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.department_id != NEW.department_id THEN
UPDATE departments
SET total_employees = total_employees - 1
WHERE department_id = OLD.department_id;
UPDATE departments
SET total_employees = total_employees + 1
WHERE department_id = NEW.department_id;
END IF;
END;
INSTEAD OF Triggers for Views
INSTEAD OF triggers are particularly useful for making complex views updatable:
-- Create a view joining multiple tables
CREATE VIEW vw_employee_details AS
SELECT
e.employee_id,
e.first_name,
e.last_name,
e.salary,
d.department_name,
d.department_id
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
-- Make the view updatable
CREATE TRIGGER trg_update_employee_view
INSTEAD OF UPDATE ON vw_employee_details
FOR EACH ROW
BEGIN
UPDATE employees
SET first_name = NEW.first_name,
last_name = NEW.last_name,
salary = NEW.salary,
department_id = NEW.department_id
WHERE employee_id = NEW.employee_id;
-- Optionally update department if name changed
IF OLD.department_name != NEW.department_name THEN
UPDATE departments
SET department_name = NEW.department_name
WHERE department_id = NEW.department_id;
END IF;
END;
Conditional Logic and Complex Business Rules
Triggers can implement sophisticated business logic with conditional execution:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_total DECIMAL(10,2),
status VARCHAR(20),
created_date TIMESTAMP
);
CREATE TABLE inventory (
product_id INT PRIMARY KEY,
quantity INT,
reserved_quantity INT DEFAULT 0
);
CREATE TABLE order_items (
order_item_id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT,
product_id INT,
quantity INT
);
-- Automatically reserve inventory when order items are added
CREATE TRIGGER trg_reserve_inventory
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
DECLARE available_qty INT;
SELECT quantity - reserved_quantity INTO available_qty
FROM inventory
WHERE product_id = NEW.product_id;
IF available_qty < NEW.quantity THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Insufficient inventory available';
END IF;
UPDATE inventory
SET reserved_quantity = reserved_quantity + NEW.quantity
WHERE product_id = NEW.product_id;
END;
-- Release inventory when order is cancelled
CREATE TRIGGER trg_release_inventory
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
IF NEW.status = 'CANCELLED' AND OLD.status != 'CANCELLED' THEN
UPDATE inventory i
JOIN order_items oi ON i.product_id = oi.product_id
SET i.reserved_quantity = i.reserved_quantity - oi.quantity
WHERE oi.order_id = NEW.order_id;
END IF;
END;
Performance Considerations and Best Practices
Keep trigger logic minimal and fast. Every trigger adds overhead to DML operations. For complex processing, consider queuing work for asynchronous processing:
CREATE TABLE trigger_queue (
queue_id INT PRIMARY KEY AUTO_INCREMENT,
operation_type VARCHAR(20),
table_name VARCHAR(50),
record_id INT,
queued_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
processed BOOLEAN DEFAULT FALSE
);
-- Lightweight trigger that queues work
CREATE TRIGGER trg_queue_complex_processing
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF NEW.department_id != OLD.department_id THEN
INSERT INTO trigger_queue (operation_type, table_name, record_id)
VALUES ('DEPT_CHANGE', 'employees', NEW.employee_id);
END IF;
END;
Avoid recursive triggers by checking conditions before performing operations. Document all triggers thoroughly, as they represent hidden business logic that can surprise developers. Use naming conventions that clearly indicate the timing, event, and purpose. Always test trigger behavior with concurrent transactions to identify locking issues.
Monitor trigger execution in production environments. A slow trigger on a high-volume table can become a critical bottleneck. Consider disabling triggers during bulk data loads and re-enabling them afterward.