by
SQL Tutorial › Performance & Objects › Triggers

SQL Triggers

AdvancedLesson 65 of 6912 min read5 queries run on MySQL 8.4

A trigger is code the database runs automatically when a row is inserted, updated or deleted in a table. Triggers can validate or adjust data before it is saved and react after it is saved: writing an audit log, keeping a stock level in sync, blocking an invalid change. They run no matter which application or user made the change.

Advertisement
In this lesson
  • Create BEFORE and AFTER triggers for INSERT, UPDATE and DELETE
  • Read the row values with NEW and OLD
  • Build an audit log and validate data with SIGNAL
  • Know when not to use triggers

Trigger syntax

MySQL
CREATE TRIGGER trigger_name{BEFORE | AFTER} {INSERT | UPDATE | DELETE}ON table_nameFOR EACH ROWtrigger_body;
EventOLD (row before)NEW (row after)
INSERTnot availablethe row being inserted
UPDATEvalues before the changevalues after the change
DELETEthe row being deletednot available

In a BEFORE trigger you can change NEW values; in an AFTER trigger they are read-only.

BEFORE INSERT: clean data

Store every new customer email in lower case:

MySQL
CREATE TRIGGER trg_customers_biBEFORE INSERT ON customersFOR EACH ROWSET NEW.email = LOWER(TRIM(NEW.email)); INSERT INTO customers (customer_id, customer_name, email, city)VALUES (8, 'Kiran Rao', '  Kiran.Rao@Example.COM ', 'Pune');SELECT customer_id, email FROM customers WHERE customer_id = 8;
ResultMySQL 8.4
mysql> CREATE TRIGGER trg_customers_bi
Query OK, 0 rows affected
mysql> INSERT INTO customers (customer_id, customer_name, email, city)
Query OK, 1 row affected
mysql> SELECT customer_id, email FROM customers WHERE customer_id = 8
customer_idemail
8kiran.rao@example.com
1 row in set
How this query works, step by step
  1. Before the row is written, the trigger replaces NEW.email with the trimmed, lower-case version.
  2. The application sent messy input, but the table stores kiran.rao@example.com.
  3. A single-statement body needs no BEGIN ... END or DELIMITER.
Advertisement

AFTER UPDATE: audit log

Record every salary change, who it was for, and the old and new values:

MySQL
CREATE TABLE salary_audit (  audit_id   INT AUTO_INCREMENT PRIMARY KEY,  emp_id     INT,  old_salary INT,  new_salary INT,  changed_by VARCHAR(80),  changed_at DATETIME DEFAULT CURRENT_TIMESTAMP); DELIMITER //CREATE TRIGGER trg_salary_auditAFTER UPDATE ON employeesFOR EACH ROWBEGIN  IF NEW.salary <> OLD.salary THEN    INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_by)    VALUES (NEW.emp_id, OLD.salary, NEW.salary, CURRENT_USER());  END IF;END //DELIMITER ; UPDATE employees SET salary = salary + 5000 WHERE dept_id = 3;UPDATE employees SET city = 'Pune' WHERE emp_id = 101;SELECT audit_id, emp_id, old_salary, new_salary, changed_by FROM salary_audit;
ResultMySQL 8.4
mysql> CREATE TABLE salary_audit (
Query OK, 0 rows affected
mysql> CREATE TRIGGER trg_salary_audit
Query OK, 0 rows affected
mysql> UPDATE employees SET salary = salary + 5000 WHERE dept_id = 3
Query OK, 2 rows affected
mysql> UPDATE employees SET city = 'Pune' WHERE emp_id = 101
Query OK, 1 row affected
mysql> SELECT audit_id, emp_id, old_salary, new_salary, changed_by FROM salar…
audit_idemp_idold_salarynew_salarychanged_by
11088800093000root@localhost
21094500050000root@localhost
2 rows in set
How this query works, step by step
  1. The trigger fires once for each row the UPDATE changes.
  2. Raising Marketing salaries changes two rows, so two audit rows are written.
  3. Changing Asha’s city fires the trigger too, but the IF sees the salary did not change and writes nothing.
  4. CURRENT_USER() records which account made the change.
Advertisement

BEFORE UPDATE: validate with SIGNAL

Reject salary cuts of more than 20%:

MySQL
DELIMITER //CREATE TRIGGER trg_salary_checkBEFORE UPDATE ON employeesFOR EACH ROWBEGIN  IF NEW.salary < OLD.salary * 0.8 THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cut above 20% needs HR approval';  END IF;END //DELIMITER ; UPDATE employees SET salary = 90000 WHERE emp_id = 102;UPDATE employees SET salary = 50000 WHERE emp_id = 102;
ResultMySQL 8.4
mysql> CREATE TRIGGER trg_salary_check
Query OK, 0 rows affected
mysql> UPDATE employees SET salary = 90000 WHERE emp_id = 102
Query OK, 1 row affected
mysql> UPDATE employees SET salary = 50000 WHERE emp_id = 102
ERROR 1644 (45000): Salary cut above 20% needs HR approval
How this query works, step by step
  1. Cutting Rahul’s salary from 98,000 to 90,000 (8%) passes.
  2. Cutting it to 50,000 is more than 20%, so SIGNAL raises error 1644 with the custom message and the update is cancelled.

AFTER INSERT: keep stock in sync

Reduce a product’s stock whenever an order line is added:

MySQL
CREATE TRIGGER trg_order_items_aiAFTER INSERT ON order_itemsFOR EACH ROWUPDATE products SET stock = stock - NEW.quantity WHERE product_id = NEW.product_id; INSERT INTO order_items (order_id, product_id, quantity) VALUES (1009, 6, 10);SELECT product_id, product_name, stock FROM products WHERE product_id = 6;
ResultMySQL 8.4
mysql> CREATE TRIGGER trg_order_items_ai
Query OK, 0 rows affected
mysql> INSERT INTO order_items (order_id, product_id, quantity) VALUES (1009,…
Query OK, 1 row affected
mysql> SELECT product_id, product_name, stock FROM products WHERE product_id …
product_idproduct_namestock
6Notebook Pack290
1 row in set
How this query works, step by step
  1. The new line orders 10 notebook packs.
  2. The trigger subtracts 10 from product 6’s stock: 300 becomes 290.
  3. A matching AFTER DELETE trigger (adding OLD.quantity back) would complete the logic.
Advertisement

AFTER DELETE: keep a history

MySQL
CREATE TABLE deleted_customers AS SELECT * FROM customers WHERE 1 = 0;CREATE TRIGGER trg_customers_adAFTER DELETE ON customersFOR EACH ROWINSERT INTO deleted_customers VALUES (OLD.customer_id, OLD.customer_name, OLD.email, OLD.city, OLD.joined_on); DELETE FROM customers WHERE customer_id = 7;SELECT * FROM deleted_customers;
ResultMySQL 8.4
mysql> CREATE TABLE deleted_customers AS SELECT * FROM customers WHERE 1 = 0
Query OK, 0 rows affected
mysql> CREATE TRIGGER trg_customers_ad
Query OK, 0 rows affected
mysql> DELETE FROM customers WHERE customer_id = 7
Query OK, 1 row affected
mysql> SELECT * FROM deleted_customers
customer_idcustomer_nameemailcityjoined_on
7Sara Thomassara.t@example.comBengaluru2025-03-02
1 row in set

WHERE 1 = 0 copies the table’s columns without any rows.

Manage triggers

TaskStatement
ListSHOW TRIGGERS FROM company;
See the codeSHOW CREATE TRIGGER trg_salary_audit;
RemoveDROP TRIGGER IF EXISTS trg_salary_audit;

Pitfalls

  • Hidden logic: someone reading INSERT INTO order_items cannot see that stock changes. Document triggers well.
  • Performance: triggers run for every row; a bulk update of a million rows runs the trigger a million times.
  • Not fired by TRUNCATE, DROP TABLE or changes made by foreign-key CASCADE actions.
  • A trigger cannot modify the same table it is defined on (other than NEW values in BEFORE triggers).

Quick quiz

1. Which row values are available in a DELETE trigger?

2. Where can you change NEW values?

3. An UPDATE changes 50 rows. How often does a FOR EACH ROW trigger run?

4. How does a trigger reject a change?

Advertisement

Triggers FAQ

What is a trigger in SQL?

A stored program that the database executes automatically before or after an INSERT, UPDATE or DELETE on a table, once for each affected row.

What is the difference between BEFORE and AFTER triggers?

A BEFORE trigger runs before the row is written and can change or reject it. An AFTER trigger runs after the change and is typically used to update other tables or write logs.

What are NEW and OLD in MySQL triggers?

NEW holds the row’s values after an INSERT or UPDATE; OLD holds the values before an UPDATE or DELETE.

Can a trigger call a stored procedure?

Yes, with CALL, as long as the procedure does not return result sets or use transaction statements.