A trigger is a database object that automatically performs an action when a specified event occurs on a table. Instead of manually running an additional SQL statement every time data changes, a trigger can respond automatically to an INSERT, UPDATE, or DELETE event.
Learning Objectives
Understand what a MySQL trigger is
Understand basic trigger syntax
Create a trigger
Understand BEFORE and AFTER trigger timing
Create triggers for INSERT, UPDATE, and DELETE events
Use FOR EACH ROW
Work with NEW and OLD values
View and remove triggers
What is a MySQL Trigger?
A trigger is automatically executed when a specified event occurs on a table.
For example, suppose a company wants to keep a record whenever a new employee is added.
Instead of manually adding a record to an audit table, an AFTER INSERT trigger can automatically create the audit entry.
A trigger generally involves:
Timing
BEFORE
AFTER
Event
INSERT
UPDATE
DELETE
Table
The table whose data change activates the trigger.
Creating Practice Tables
For this article, we'll use an EmployeesTrigger table and an EmployeeAudit table.
USE trainingdbc;
CREATE TABLE EmployeesTrigger (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
employee_name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10,2)
);
Now create the audit table:
CREATE TABLE EmployeeAudit (
audit_id INT AUTO_INCREMENT PRIMARY KEY,
employee_id INT,
action_type VARCHAR(50),
action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
The first table stores employee information, while the second will store records generated by our triggers.
Basic Trigger Syntax
The general structure of a trigger is:
CREATE TRIGGER trigger_name
BEFORE | AFTER INSERT | UPDATE | DELETE
ON table_name
FOR EACH ROW
BEGIN
-- statements
END;
The exact syntax depends on the timing, event, and action required.
BEFORE vs AFTER Triggers
MySQL triggers can execute either before or after a table event.
BEFORE
A BEFORE trigger runs before the database operation is completed.
For example:
CREATE TRIGGER before_employee_insert
BEFORE INSERT
ON EmployeesTrigger
FOR EACH ROW
SET NEW.employee_name = TRIM(NEW.employee_name);
This trigger removes leading and trailing spaces from an employee name before the record is stored.
If you insert:
INSERT INTO EmployeesTrigger
(employee_name, department, salary)
VALUES
(' John ', 'IT', 5000.00);
MySQL stores the employee name as:
John
instead of retaining the surrounding spaces.
AFTER
An AFTER trigger runs after the database operation has completed.
For example, an AFTER INSERT trigger can record that a new employee was added.
AFTER INSERT Trigger
Create the trigger:
CREATE TRIGGER after_employee_insert
AFTER INSERT
ON EmployeesTrigger
FOR EACH ROW
INSERT INTO EmployeeAudit (
employee_id,
action_type
)
VALUES (
NEW.employee_id,
'Employee Added'
);
Here, NEW.employee_id refers to the ID of the employee that was just inserted.
Now insert a new employee:
INSERT INTO EmployeesTrigger
(employee_name, department, salary)
VALUES
('Priya', 'Finance', 6000.00);
Check the employee:
SELECT * FROM EmployeesTrigger;
Then check the audit table:
SELECT * FROM EmployeeAudit;
The audit table should contain an automatically created record similar to:
audit_id | employee_id | action_type | action_time |
1 | 2 | Employee Added | timestamp |
The exact IDs depend on the records already in your table.
INSERT, UPDATE, and DELETE Trigger Events
Triggers can respond to three main table events.
INSERT
An INSERT trigger executes when a new row is added.
AFTER INSERT
The previous example uses this event to create an audit record whenever a new employee is added.
UPDATE
An UPDATE trigger executes when an existing row is modified.
For example:
CREATE TRIGGER after_employee_update
AFTER UPDATE
ON EmployeesTrigger
FOR EACH ROW
INSERT INTO EmployeeAudit (
employee_id,
action_type
)
VALUES (
NEW.employee_id,
'Employee Updated'
);
Now update an employee:
UPDATE EmployeesTrigger
SET salary = 6500.00
WHERE employee_name = 'Priya';
Check the audit table:
SELECT * FROM EmployeeAudit;
You should now see an Employee Updated audit record.
OLD and NEW Values
Triggers can access values associated with the row that caused the trigger to execute.
NEW refers to the new values associated with an inserted or updated row.
OLD refers to values that existed before an update or deletion.
For an update, both can be useful.
For example:
CREATE TABLE SalaryAudit (
audit_id INT AUTO_INCREMENT PRIMARY KEY,
employee_id INT,
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2)
);
Create a trigger:
CREATE TRIGGER employee_salary_change
AFTER UPDATE
ON EmployeesTrigger
FOR EACH ROW
INSERT INTO SalaryAudit (
employee_id,
old_salary,
new_salary
)
VALUES (
NEW.employee_id,
OLD.salary,
NEW.salary
);
If an employee's salary changes from 6000.00 to 6500.00, the audit table can store both values.
DELETE Trigger
A DELETE trigger can run when a record is removed.
For example:
CREATE TRIGGER before_employee_delete
BEFORE DELETE
ON EmployeesTrigger
FOR EACH ROW
INSERT INTO EmployeeAudit (
employee_id,
action_type
)
VALUES (
OLD.employee_id,
'Employee Deleted'
);
Here, we use:
OLD.employee_id
because the employee record is being deleted.
Delete an employee:
DELETE FROM EmployeesTrigger
WHERE employee_name = 'Priya';
Then:
SELECT * FROM EmployeeAudit;
The audit table should include:
Employee Deleted
Understanding FOR EACH ROW
MySQL uses:
FOR EACH ROW
to indicate that the trigger executes for each row affected by the triggering statement.
For example, if an operation affects three rows, a row-level trigger can execute once for each affected row.
Consider:
UPDATE EmployeesTrigger
SET salary = salary + 500
WHERE department = 'IT';
If three employees belong to IT and are updated, the AFTER UPDATE trigger runs for each of those affected rows.
Viewing Triggers
You can view triggers available in the current database using:
SHOW TRIGGERS;
This can display information such as:
Trigger name
Event
Table
Trigger statement
Timing
You can also filter by database: SHOW TRIGGERS FROM trainingdbc;
Dropping a Trigger
When a trigger is no longer required, use DROP TRIGGER.
For example:
DROP TRIGGER after_employee_insert;
To avoid an error when the trigger doesn't exist:
DROP TRIGGER IF EXISTS after_employee_insert;
Dropping a trigger removes the trigger itself. It does not delete the table on which the trigger was defined.
BEFORE vs AFTER
Trigger Timing | Purpose |
BEFORE INSERT | Runs before a new row is inserted |
AFTER INSERT | Runs after a new row is inserted |
BEFORE UPDATE | Runs before an existing row is updated |
AFTER UPDATE | Runs after an existing row is updated |
BEFORE DELETE | Runs before a row is deleted |
AFTER DELETE | Runs after a row is deleted |
The appropriate timing depends on what the trigger needs to accomplish.
Common Mistakes
Creating the trigger on the wrong table
Confusing BEFORE and AFTER
Using OLD or NEW incorrectly
Forgetting FOR EACH ROW
Creating duplicate triggers with the same name
Forgetting that triggers execute automatically
Creating trigger logic that causes unintended changes to other data
When testing triggers, use practice tables and verify the results after each operation.
Still need help?
Contact us