Chapter 34 of 35

Triggers

Suppose HR asks: "Whenever anyone changes an employee's salary, record the old salary, the new salary, who changed it and when." You could ask every developer to remember to write an audit row after every salary update, in every application, forever. Someone will forget. Or you could tell the database to do it automatically, every single time, no matter who runs the UPDATE or from where.

That is what a trigger does. In this chapter we will write BEFORE and AFTER triggers for INSERT, UPDATE and DELETE, use the special OLD and NEW rows, build an audit log, clean and validate data, archive deleted rows, control the order of multiple triggers, and learn the limitations that catch people out.

Triggers are stored in the database, so these examples were run in a copy of company_db on MySQL 8.4. All outputs are real. This chapter builds on the Stored Procedures & Functions chapter (DELIMITER, BEGIN ... END, IF, SIGNAL).


What Is a Trigger?

A trigger is a named block of SQL that is attached to a table and runs automatically when a specific event (INSERT, UPDATE or DELETE) happens on that table, either just before or just after each affected row is changed.

The key difference from a stored procedure: nobody calls a trigger. The database fires it as a side effect of a data change. A trigger is defined by three choices:

Choice

Options

Timing

BEFORE or AFTER the row is changed

Event

INSERT, UPDATE or DELETE

Table

One permanent table (not a view, not a temporary table)

So there are six kinds of trigger per table: BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE and AFTER DELETE.

Common uses:

  • Auditing: log who changed what and when.

  • Data cleaning and defaults: trim and lowercase emails, fill in missing values.

  • Complex validation: rules that a CHECK constraint cannot express, such as comparing the new value with the old one.

  • Archiving: copy deleted rows into a history table.

  • Keeping derived data in sync: for example, updating a summary table when orders change.


Syntax

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
[{FOLLOWS | PRECEDES} other_trigger]
trigger_body;          -- one statement, or BEGIN ... END

FOR EACH ROW is mandatory in MySQL: every MySQL trigger is a row-level trigger, which runs once for each row the statement touches. (Some other databases, such as PostgreSQL, SQL Server and Oracle, also offer statement-level triggers that run once per statement; MySQL does not.)

OLD and NEW

Inside the body, two special names give access to the row being changed:

Event

OLD (row before the change)

NEW (row after the change)

INSERT

Not available

The row being inserted

UPDATE

The row's values before the update (read-only)

The row's values after the update

DELETE

The row being deleted (read-only)

Not available

In a BEFORE trigger you may change NEW values with SET NEW.col = ..., and the changed value is what gets stored. In an AFTER trigger the row is already written, so NEW is read-only. Using a row that does not exist is caught when you create the trigger:

CREATE TRIGGER t_x BEFORE INSERT ON customers
FOR EACH ROW SET NEW.name = OLD.name;
ERROR 1363 (HY000): There is no OLD row in on INSERT trigger

Example 1: An Audit Trail (AFTER UPDATE)

First, a table to hold the log:

CREATE TABLE salary_audit (
  audit_id   INT AUTO_INCREMENT PRIMARY KEY,
  emp_id     INT NOT NULL,
  old_salary DECIMAL(10,2),
  new_salary DECIMAL(10,2),
  changed_by VARCHAR(100),
  changed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Now the trigger. It fires after every UPDATE on employees, but writes a log row only if the salary actually changed:

DELIMITER //
CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary <> OLD.salary THEN
    INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_by)
    VALUES (OLD.emp_id, OLD.salary, NEW.salary, CURRENT_USER());
  END IF;
END //
DELIMITER ;

Give everyone in Sales (dept 20) a 10% raise, and separately change one employee's city:

UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 20;
UPDATE employees SET city = 'Thane' WHERE emp_id = 5;

SELECT audit_id, emp_id, old_salary, new_salary, changed_by FROM salary_audit;
+----------+--------+------------+------------+-----------------+
| audit_id | emp_id | old_salary | new_salary | changed_by      |
+----------+--------+------------+------------+-----------------+
|        1 |      4 |  110000.00 |  121000.00 | tutor@localhost |
|        2 |      5 |   62000.00 |   68200.00 | tutor@localhost |
|        3 |      6 |   58000.00 |   63800.00 | tutor@localhost |
+----------+--------+------------+------------+-----------------+
3 rows in set (0.00 sec)

What happened, step by step:

  1. The first UPDATE changed 3 rows, so the trigger fired 3 times, once per row, each time with that row's OLD and NEW values. This is what "FOR EACH ROW" means.

  2. The second UPDATE also fired the trigger once (for Vikram), but his salary did not change, so the IF skipped the INSERT. Without that IF, the log would fill up with useless rows.

  3. changed_by records the MySQL account (tutor@localhost in our setup). Note that a web application usually connects with one shared account, so to log the real end user, the application must pass that information in (for example through a column or a session variable).

The audit log is written in the same transaction as the update. If the update is rolled back, so is its audit row; they can never disagree.

One subtle point: if a column can be NULL, NEW.col <> OLD.col is NULL (not true) when either side is NULL, so a change from NULL to a value would not be logged. Use the NULL-safe operator instead: IF NOT (NEW.col <=> OLD.col) THEN. (salary is NOT NULL, so the simple form is fine here.)


Example 2: Cleaning Data Before It Is Stored (BEFORE INSERT)

Emails typed by people arrive with random capitals and spaces. A BEFORE INSERT trigger can fix them before the row is written:

DELIMITER //
CREATE TRIGGER trg_emp_clean_email
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  SET NEW.email = LOWER(TRIM(NEW.email));
END //
DELIMITER ;

INSERT INTO employees (emp_id, name, email, dept_id, manager_id, salary, hire_date, city)
VALUES (11, 'Tanvi Shah', '  Tanvi.Shah@Company.COM ', 10, 1, 72000, '2025-02-03', 'Pune');

SELECT emp_id, name, email FROM employees WHERE emp_id = 11;
+--------+------------+------------------------+
| emp_id | name       | email                  |
+--------+------------+------------------------+
|     11 | Tanvi Shah | tanvi.shah@company.com |
+--------+------------+------------------------+
1 row in set (0.00 sec)

The application sent a messy value, but the stored value is clean. This must be a BEFORE trigger: by the time an AFTER trigger runs, the row has already been saved.

Another example, filling in a default that depends on a condition. Customers with no city get 'Unknown':

DELIMITER //
CREATE TRIGGER trg_cust_city
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
  IF NEW.city IS NULL THEN
    SET NEW.city = 'Unknown';
  END IF;
END //
DELIMITER ;

INSERT INTO customers VALUES (106, 'Farah Khan', NULL, '2024-05-01');
SELECT * FROM customers WHERE customer_id = 106;
+-------------+------------+---------+-------------+
| customer_id | name       | city    | signup_date |
+-------------+------------+---------+-------------+
|         106 | Farah Khan | Unknown | 2024-05-01  |
+-------------+------------+---------+-------------+
1 row in set (0.00 sec)

(A column DEFAULT only applies when the column is omitted; here the INSERT explicitly passed NULL, so only a trigger can replace it.)


Example 3: Enforcing a Business Rule (BEFORE UPDATE + SIGNAL)

Rule: a salary may never be cut by more than 20% in one update. A CHECK constraint cannot express this, because it can only see the new row, not the old one. A trigger sees both:

DELIMITER //
CREATE TRIGGER trg_salary_check
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < OLD.salary * 0.8 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Salary cannot be cut by more than 20%';
  END IF;
END //
DELIMITER ;

UPDATE employees SET salary = 40000 WHERE emp_id = 9;   -- Arjun: 82000 -> 40000
ERROR 1644 (45000): Salary cannot be cut by more than 20%

Arjun's salary is still 82,000. Now a multi-row update where only one row breaks the rule. In HR (dept 30), setting everyone to 50,000 would cut Karan from 70,000 (a 28.6% cut) but raise Meera from 48,000:

UPDATE employees SET salary = 50000 WHERE dept_id = 30;
ERROR 1644 (45000): Salary cannot be cut by more than 20%
SELECT emp_id, name, salary FROM employees WHERE dept_id = 30;
+--------+-------------+----------+
| emp_id | name        | salary   |
+--------+-------------+----------+
|      7 | Karan Patel | 70000.00 |
|      8 | Meera Nair  | 48000.00 |
+--------+-------------+----------+
2 rows in set (0.00 sec)

Neither row changed, not even Meera's valid raise. With InnoDB, if a trigger fails, the whole statement fails and every change it made (including changes made by other triggers) is undone. A statement is atomic, just like a transaction.


Example 4: Archiving Deleted Rows (AFTER DELETE)

Before deleting orders for good, keep a copy:

CREATE TABLE orders_archive LIKE orders;
ALTER TABLE orders_archive ADD COLUMN deleted_at DATETIME DEFAULT CURRENT_TIMESTAMP;

DELIMITER //
CREATE TRIGGER trg_orders_archive
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
  INSERT INTO orders_archive (order_id, customer_id, order_date, amount, status)
  VALUES (OLD.order_id, OLD.customer_id, OLD.order_date, OLD.amount, OLD.status);
END //
DELIMITER ;

DELETE FROM orders WHERE status = 'Cancelled';
SELECT order_id, customer_id, amount, status FROM orders_archive;
+----------+-------------+---------+-----------+
| order_id | customer_id | amount  | status    |
+----------+-------------+---------+-----------+
|     1003 |         101 | 1200.00 | Cancelled |
+----------+-------------+---------+-----------+
1 row in set (0.00 sec)

In a DELETE trigger only OLD exists, because there is no "new" row. We used AFTER rather than BEFORE because the archive should record deletes that actually happened. (With InnoDB, a BEFORE trigger's archive row would also be rolled back if the delete then failed, for example because of a foreign key, but AFTER states the intent clearly and avoids wasted work.)


BEFORE or AFTER?

Use BEFORE when you want to...

Use AFTER when you want to...

Change the values being stored (SET NEW.col = ...)

React to a change that has definitely happened

Reject invalid data early with SIGNAL

Write to other tables: audit logs, archives, summaries

Fill in derived or default values

Use values that are only known after the write (such as a generated AUTO_INCREMENT id in NEW.id)

The complete order of events for each row is:

BEFORE trigger  ->  row is written (constraints checked)  ->  AFTER trigger
      |                          |                                  |
  if it fails,           if it fails, the AFTER            if it fails, the row
  the row is never       trigger does not run              change is undone too
  written

We can see the last case directly. This AFTER INSERT trigger rejects very large orders:

DELIMITER //
CREATE TRIGGER trg_after_fail AFTER INSERT ON orders FOR EACH ROW
BEGIN
  IF NEW.amount > 100000 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Order needs manual approval';
  END IF;
END //
DELIMITER ;

INSERT INTO orders VALUES (1009, 103, '2024-04-10', 250000, 'Pending');
ERROR 1644 (45000): Order needs manual approval

The row had already been written when the AFTER trigger ran, yet SELECT COUNT(*) FROM orders WHERE order_id = 1009 returns 0: InnoDB rolled the insert back. (Validation like this is still better placed in a BEFORE trigger, so the database does not do the work only to undo it.)


Multiple Triggers on the Same Event

Since MySQL 5.7 a table can have several triggers for the same timing and event. By default they run in the order they were created. FOLLOWS and PRECEDES let you place a new trigger relative to an existing one. We already have trg_cust_city (BEFORE INSERT on customers). Let us add two logging triggers:

CREATE TABLE trg_log (msg VARCHAR(100));

DELIMITER //
CREATE TRIGGER trg_a BEFORE INSERT ON customers FOR EACH ROW
  INSERT INTO trg_log VALUES (CONCAT('trg_a saw city = ', COALESCE(NEW.city, 'NULL')));
//
CREATE TRIGGER trg_b BEFORE INSERT ON customers FOR EACH ROW
PRECEDES trg_cust_city
  INSERT INTO trg_log VALUES (CONCAT('trg_b saw city = ', COALESCE(NEW.city, 'NULL')));
//
DELIMITER ;

INSERT INTO customers VALUES (107, 'Dev Malhotra', NULL, '2024-06-11');
SELECT * FROM trg_log;
+--------------------------+
| msg                      |
+--------------------------+
| trg_b saw city = NULL    |
| trg_a saw city = Unknown |
+--------------------------+
2 rows in set (0.00 sec)

The execution order was trg_b → trg_cust_city → trg_a. trg_b ran before the city was filled in, so it saw NULL; trg_a, created last, ran after it and saw 'Unknown'. Each BEFORE trigger sees the NEW values as modified by the triggers before it. When order matters, state it explicitly with FOLLOWS/PRECEDES rather than relying on creation order.


Viewing and Dropping Triggers

SHOW TRIGGERS;                          -- all triggers in the current database
SHOW TRIGGERS LIKE 'orders';            -- triggers on tables matching the pattern
SHOW CREATE TRIGGER trg_salary_audit;   -- full definition

For a compact overview, query the data dictionary:

SELECT TRIGGER_NAME, EVENT_MANIPULATION, ACTION_TIMING,
       EVENT_OBJECT_TABLE, ACTION_ORDER
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING, EVENT_MANIPULATION, ACTION_ORDER;
+---------------------+--------------------+---------------+--------------------+--------------+
| TRIGGER_NAME        | EVENT_MANIPULATION | ACTION_TIMING | EVENT_OBJECT_TABLE | ACTION_ORDER |
+---------------------+--------------------+---------------+--------------------+--------------+
| trg_b               | INSERT             | BEFORE        | customers          |            1 |
| trg_cust_city       | INSERT             | BEFORE        | customers          |            2 |
| trg_a               | INSERT             | BEFORE        | customers          |            3 |
| trg_emp_clean_email | INSERT             | BEFORE        | employees          |            1 |
| trg_salary_check    | UPDATE             | BEFORE        | employees          |            1 |
| trg_salary_audit    | UPDATE             | AFTER         | employees          |            1 |
| trg_after_fail      | INSERT             | AFTER         | orders             |            1 |
| trg_orders_archive  | DELETE             | AFTER         | orders             |            1 |
+---------------------+--------------------+---------------+--------------------+--------------+
8 rows in set (0.00 sec)

(ACTION_TIMING and EVENT_MANIPULATION are stored as enumerations, so they sort in their defined order, BEFORE before AFTER and INSERT, UPDATE, DELETE, not alphabetically.) ACTION_ORDER shows the firing order among triggers that share a timing and event.

Like procedures, triggers cannot be edited in place: DROP and re-CREATE. Dropping a table drops its triggers. For example, the test trigger from the previous section is no longer needed:

DROP TRIGGER IF EXISTS trg_after_fail;

Limitations and Gotchas

1. A trigger cannot modify its own table

A tempting idea: an AFTER INSERT trigger that "fixes" the inserted row with an UPDATE:

DELIMITER //
CREATE TRIGGER trg_bad AFTER INSERT ON customers FOR EACH ROW
BEGIN
  UPDATE customers SET city = 'Unknown' WHERE customer_id = NEW.customer_id;
END //
DELIMITER ;

INSERT INTO customers VALUES (108, 'Ira Bose', NULL, '2024-07-01');
ERROR 1442 (HY000): Can't update table 'customers' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

The trigger was created without complaint; the error appears only when it fires, and the INSERT fails with it (customer 108 was not added). The correct approach is the BEFORE INSERT trigger with SET NEW.city = ... shown earlier.

2. Foreign key cascades do not fire triggers

If deleting a parent row removes child rows through ON DELETE CASCADE, DELETE triggers on the child table do not run for those rows. We tested this with a parent table p, a child table c (with ON DELETE CASCADE) and an AFTER DELETE logging trigger on c: deleting a parent removed its two children, but the log stayed empty. Deleting a child directly did log a row. If you rely on triggers for auditing, cascades can silently bypass them.

3. TRUNCATE does not fire DELETE triggers

TRUNCATE TABLE drops and recreates the table's data rather than deleting row by row, so no DELETE trigger runs (confirmed in the same test). Only DELETE fires them.

4. No transactions inside triggers

A trigger runs inside the statement's transaction. It cannot use START TRANSACTION, COMMIT or ROLLBACK, and cannot run statements that commit implicitly (such as CREATE TABLE). It also cannot return a result set to the client.

5. Hidden work and hidden cost

Triggers are invisible to whoever runs the INSERT/UPDATE/DELETE. An UPDATE of a million rows fires a row-level trigger a million times. A developer debugging "why does my insert change this other table?" may not think to look for a trigger. Keep triggers small, fast and well documented.


Triggers vs Constraints vs Procedures

Need

Best tool

Simple rule on one row (not null, unique, range, valid reference)

Constraints: NOT NULL, UNIQUE, CHECK, FOREIGN KEY (faster, declarative, visible in the schema)

Rule comparing old and new values, or needing other tables

BEFORE trigger with SIGNAL

Something that must happen on every change, whoever makes it (auditing)

Trigger

A multi-step business operation started on purpose (transfer, place order)

Stored procedure or application code with a transaction

A good rule: use a constraint if one can do the job, a trigger if the rule must be enforced automatically and a constraint cannot express it, and a procedure or application code for everything that is a deliberate action.


Common Mistakes

  • Using an AFTER trigger to change NEW values (not allowed; use BEFORE).

  • Updating the trigger's own table from inside the trigger (error 1442).

  • Comparing nullable columns with <> and missing changes to or from NULL; use <=>.

  • Assuming cascaded deletes or TRUNCATE will fire DELETE triggers.

  • Forgetting DELIMITER when the body has several statements.

  • Putting heavy logic (or slow queries) in triggers on busy tables.

  • Relying on creation order when several triggers share an event; use FOLLOWS/PRECEDES.


Interview Questions

What is a trigger?

A stored program attached to a table that runs automatically before or after an INSERT, UPDATE or DELETE on that table, once for each affected row in MySQL.

What is the difference between BEFORE and AFTER triggers?

A BEFORE trigger runs before the row is written and can change NEW values or reject the change. An AFTER trigger runs after the row is written; it cannot change NEW, and is used for logging or updating other tables.

What are OLD and NEW?

References to the row being changed. OLD holds the values before the change (UPDATE, DELETE); NEW holds the values after the change (INSERT, UPDATE). NEW can be modified only in BEFORE triggers.

Does MySQL support statement-level triggers?

No. All MySQL triggers are row-level (FOR EACH ROW). An UPDATE that changes 100 rows fires the trigger 100 times.

Can a trigger update the same table it is defined on?

No. MySQL raises error 1442. To change the row being inserted or updated, modify NEW in a BEFORE trigger.

What happens if a trigger fails?

The statement that fired it fails. With InnoDB, all changes made by that statement, including the triggering row change and other triggers' work, are rolled back.

Do triggers fire on TRUNCATE or foreign key cascades?

No. TRUNCATE does not fire DELETE triggers, and rows changed by cascading foreign key actions do not activate triggers in MySQL.

When would you use a trigger instead of a CHECK constraint?

When the rule needs the old value (for example, limiting a salary cut), data from other tables, or an automatic side effect such as an audit log. Otherwise prefer the constraint.

What are the disadvantages of triggers?

Hidden logic that is easy to forget during debugging, per-row overhead on large changes, limitations (no transactions, cannot modify own table), and harder testing.


Summary

  • A trigger runs automatically BEFORE or AFTER an INSERT, UPDATE or DELETE on a table; MySQL triggers are always row-level (FOR EACH ROW).

  • OLD is the row before the change, NEW the row after; only BEFORE triggers can modify NEW.

  • BEFORE triggers clean, default and validate data (with SIGNAL); AFTER triggers write audit logs, archives and summaries.

  • If a trigger fails, the whole statement is undone (InnoDB).

  • Several triggers can share an event; control their order with FOLLOWS/PRECEDES.

  • Limitations: no changes to the trigger's own table, no transaction control, not fired by TRUNCATE or FK cascades.

  • Prefer constraints when they are enough; use triggers for rules and side effects that must always happen.

Next, in Top SQL Interview Queries, we put everything from this tutorial together and solve the queries that interviewers ask most often.