What Are Triggers?
A trigger is a database object associated with a table that automatically executes a block of SQL when a specific DML event (INSERT, UPDATE, or DELETE) occurs. Unlike stored procedures, triggers are never called directly — they fire implicitly as a reaction to data changes.
Every trigger is tied to exactly one table and one event: INSERT, UPDATE, or DELETE. It fires either BEFORE the change is written (BEFORE trigger) or AFTER it has been written (AFTER trigger). You choose based on whether you need to inspect or modify the data before it is saved, or react to it after it is committed.
Creating a Trigger
The general syntax is: CREATE TRIGGER name BEFORE|AFTER INSERT|UPDATE|DELETE ON table FOR EACH ROW BEGIN ... END
DELIMITER $$
-- AFTER INSERT trigger: log new employee to audit table
CREATE TRIGGER trg_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_log (table_name, operation, record_id, changed_at, changed_by)
VALUES ('employees', 'INSERT', NEW.id, NOW(), CURRENT_USER());
END$$
-- BEFORE UPDATE trigger: prevent salary from being decreased
CREATE TRIGGER trg_no_salary_decrease
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF NEW.salary < OLD.salary THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary cannot be decreased via UPDATE';
END IF;
END$$
-- AFTER DELETE trigger: archive deleted record
CREATE TRIGGER trg_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employees_archive
(id, name, department, salary, deleted_at)
VALUES
(OLD.id, OLD.name, OLD.department, OLD.salary, NOW());
END$$
DELIMITER ;BEFORE vs AFTER Triggers
| Timing | Fires When | Can Modify NEW Row? | Typical Use Cases |
|---|---|---|---|
| BEFORE | Before the row is written to disk | Yes — set NEW.col = value | Validation, data normalization, computed columns, rejecting invalid values |
| AFTER | After the row is successfully written | No — row already saved | Audit logging, cascading updates to other tables, sending notifications |
DELIMITER $$
-- BEFORE INSERT: normalize email to lowercase and auto-set created_at
CREATE TRIGGER trg_normalize_user
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SET NEW.email = LOWER(TRIM(NEW.email));
SET NEW.created_at = COALESCE(NEW.created_at, NOW());
END$$
DELIMITER ;NEW and OLD Row References
Inside a trigger body, two special pseudo-rows give you access to the data being changed:
| Reference | Available In | Meaning |
|---|---|---|
| NEW | INSERT, UPDATE | The row as it will be after the operation. In BEFORE triggers you can modify NEW columns. |
| OLD | UPDATE, DELETE | The row as it was before the operation. Read-only — you cannot modify OLD. |
DELIMITER $$
-- Full audit trail: record what changed, old value, and new value
CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.salary != NEW.salary THEN
INSERT INTO salary_changes
(employee_id, old_salary, new_salary, changed_at, changed_by)
VALUES
(NEW.id, OLD.salary, NEW.salary, NOW(), CURRENT_USER());
END IF;
END$$
DELIMITER ;Practical Use Cases
1. Automatic updated_at timestamp
DELIMITER $$
CREATE TRIGGER trg_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
SET NEW.updated_at = NOW();
END$$
DELIMITER ;
-- Note: In MySQL you can also achieve this with:
-- updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-- But a trigger gives you more control (e.g. conditional updates).2. Cascade stock deduction on order
DELIMITER $$
CREATE TRIGGER trg_deduct_stock
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
UPDATE products
SET stock = stock - NEW.quantity
WHERE id = NEW.product_id;
-- Warn (but allow) if stock goes negative
IF (SELECT stock FROM products WHERE id = NEW.product_id) < 0 THEN
INSERT INTO stock_alerts (product_id, alert_time)
VALUES (NEW.product_id, NOW());
END IF;
END$$
DELIMITER ;3. PostgreSQL trigger (PL/pgSQL)
-- Step 1: Create the trigger function
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Step 2: Attach the function to the table as a trigger
CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();
-- Drop a trigger (PostgreSQL)
DROP TRIGGER trg_set_updated_at ON employees;In PostgreSQL, the logic goes in a separate trigger function that returns TRIGGER. The CREATE TRIGGER statement then attaches that function to a table event. This allows you to reuse the same trigger function on multiple tables — a cleaner design than MySQL's inline approach.
Cautions: When Not to Use Triggers
- Hidden logic — triggers fire silently. A developer querying the table won't see the trigger logic unless they specifically look for it, making the system harder to understand and debug.
- Performance impact — every INSERT/UPDATE/DELETE on the table executes the trigger. A poorly written trigger can add significant overhead to write-heavy tables.
- Hard to debug — trigger errors surface as cryptic database errors, and stepping through trigger logic in a debugger is not as straightforward as application code.
- Cascading chains — a trigger that modifies another table can fire a second trigger, which fires a third, and so on. These chains are extremely hard to reason about and can cause unexpected behavior or infinite loops.
- Testing difficulty — triggers make unit testing harder because test code must be aware of all the side effects that fire on simple INSERT/UPDATE operations.
Triggers are best suited for infrastructure concerns (audit trails, updated_at stamps) that should happen for every write, regardless of application. Avoid encoding business logic in triggers — put that in your application's service layer or in explicit stored procedures called deliberately.
Summary
📋 Summary
- A trigger fires automatically on INSERT, UPDATE, or DELETE — you never call it directly.
- BEFORE triggers run before the write and can modify
NEWcolumn values; AFTER triggers run after and are read-only. NEWrefers to the incoming row (INSERT/UPDATE);OLDrefers to the existing row (UPDATE/DELETE).- Great use cases: audit logging, automatic timestamps, cascading stock updates.
- In PostgreSQL, triggers use a two-step approach: create a trigger function, then attach it with
CREATE TRIGGER ... EXECUTE FUNCTION. - Avoid complex business logic in triggers — they are hidden, hard to debug, and can cascade unexpectedly.
FAQ
Yes — a trigger body can call a stored procedure using CALL procedure_name(...) in MySQL. In PostgreSQL, a trigger function can call other functions. However, calling long-running procedures from triggers is risky because triggers run inside the transaction of the triggering statement, which means locks are held longer than expected.
In PostgreSQL, yes — multiple triggers can fire for the same event on the same table, and they execute in alphabetical order by trigger name. In MySQL, each table can have at most one trigger for each combination of event (INSERT/UPDATE/DELETE) and timing (BEFORE/AFTER) — so a maximum of 6 triggers per table. In MySQL 5.7+ you can use the FOLLOWS and PRECEDES clauses to order triggers on the same event (MySQL supports multiple triggers per event from 5.7).
In MySQL and PostgreSQL, FOR EACH ROW triggers fire once per affected row — so a DELETE that removes 10,000 rows fires the trigger 10,000 times. This can be extremely slow for bulk operations. If you run large bulk deletes/updates frequently, consider disabling triggers temporarily (if the database supports it) or using batch stored procedures instead of row-level triggers.
In MySQL: SHOW TRIGGERS FROM my_database; or query information_schema.TRIGGERS. In PostgreSQL: SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema = 'public';. Both return the trigger name, the event it fires on, the associated table, and timing information.