Database applications often need to perform certain actions automatically when data changes. For example, whenever a new employee is added, you may want to record that event in an audit table. Similarly, when a product price changes, you may want to keep a history of the previous and new prices.
MySQL Triggers provide a convenient way to automate such database operations.
A trigger is a database object that automatically executes a predefined SQL statement when a specific event occurs on a table. Common triggering events include INSERT, UPDATE, and DELETE.
In this article, we'll learn what MySQL triggers are, how they work, their syntax, different types, practical examples, and important considerations when using them.
What Is a MySQL Trigger?
A MySQL trigger is a set of SQL statements that automatically executes in response to a specified event on a table.
Unlike a stored procedure, you don't call a trigger directly. MySQL invokes it automatically when its associated event occurs.
For example, suppose you have an employees table. You could create a trigger that automatically records a message in an audit table whenever a new employee is inserted.
The basic concept is:
Database Event
↓
Trigger Fires
↓
Trigger Action Executes
This makes triggers useful for tasks such as maintaining audit records, validating data, and automatically updating related information.
*MySQL Trigger Syntax
*
The general syntax for creating a trigger is:
CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
trigger_body;
Here:
trigger_name specifies the name of the trigger.
trigger_time can be BEFORE or AFTER.
trigger_event can be INSERT, UPDATE, or DELETE.
table_name specifies the table associated with the trigger.
FOR EACH ROW means the trigger executes for each affected row.
trigger_body contains the SQL statements executed by the trigger.
Types of MySQL Triggers
MySQL triggers can be categorized according to when they execute and which database event activates them.
The main combinations are:
BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE
Let's understand each one.
1. BEFORE INSERT Trigger
A BEFORE INSERT trigger executes before a new row is inserted into a table.
It can be useful when you need to validate or modify values before they are stored.
Example
Create an employee table:
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10,2)
);
Now create a trigger:
DELIMITER //
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF NEW.salary < 0 THEN
SET NEW.salary = 0;
END IF;
END //
DELIMITER ;
Here, NEW.salary represents the value that is about to be inserted.
If an application attempts to insert a negative salary, the trigger changes it to 0.
2. AFTER INSERT Trigger
An AFTER INSERT trigger runs after a new row has been inserted successfully.
It is commonly useful for maintaining audit or logging tables.
For example, create an audit table:
CREATE TABLE employee_log (
log_id INT AUTO_INCREMENT PRIMARY KEY,
employee_id INT,
action VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Now create an AFTER INSERT trigger:
DELIMITER //
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_log (employee_id, action)
VALUES (NEW.id, 'Employee Added');
END //
DELIMITER ;
Whenever a new employee is added, a corresponding record is automatically created in employee_log.
3. BEFORE UPDATE Trigger
A BEFORE UPDATE trigger executes before an existing row is updated.
For example, you might want to prevent a product price from becoming negative.
DELIMITER //
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF NEW.salary < 0 THEN
SET NEW.salary = 0;
END IF;
END //
DELIMITER ;
The NEW keyword represents the value after the proposed update.
4. AFTER UPDATE Trigger
An AFTER UPDATE trigger executes after a row has been updated.
This is especially useful for maintaining change histories.
Suppose you want to record salary changes. You can create a history table:
CREATE TABLE salary_history (
history_id INT AUTO_INCREMENT PRIMARY KEY,
employee_id INT,
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Then create the trigger:
DELIMITER //
CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.salary <> NEW.salary THEN
INSERT INTO salary_history
(employee_id, old_salary, new_salary)
VALUES
(OLD.id, OLD.salary, NEW.salary);
END IF;
END //
DELIMITER ;
Now every salary change can be recorded automatically.
5. BEFORE DELETE Trigger
A BEFORE DELETE trigger runs before a row is deleted.
It can be used for validation or for performing certain operations before deletion.
For example:
DELIMITER //
CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_log (employee_id, action)
VALUES (OLD.id, 'Employee Deleted');
END //
DELIMITER ;
Here, OLD.id refers to the ID of the row that is about to be deleted.
6. AFTER DELETE Trigger
An AFTER DELETE trigger executes after a row has been deleted.
For example:
DELIMITER //
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_log (employee_id, action)
VALUES (OLD.id, 'Delete Completed');
END //
DELIMITER ;
The OLD keyword is important when working with deleted records because the row no longer exists in the main table.
Understanding OLD and NEW
Two important keywords used with MySQL triggers are OLD and NEW.
NEW
NEW represents the new value of a row.
It is commonly used with:
INSERT
UPDATE
Example:
NEW.salary
OLD
OLD represents the existing value before a change.
It is commonly used with:
UPDATE
DELETE
Example:
OLD.salary
A simple way to remember them is:
OLD → Previous value
NEW → New value
For an INSERT, there is no OLD row.
For a DELETE, there is no NEW row.
Testing a MySQL Trigger
Let's test the employee trigger.
First, insert an employee:
INSERT INTO employees (name, salary)
VALUES ('Alex', 50000);
Now check the employee table:
SELECT * FROM employees;
You can also check the audit table:
SELECT * FROM employee_log;
If the AFTER INSERT trigger is working correctly, an entry should automatically appear in the log table.
How to Show Existing Triggers
You can use the following command to display triggers:
SHOW TRIGGERS;
You can also retrieve trigger information from the database metadata.
This is useful when working with an existing database and you need to determine which triggers have already been created.
How to Delete a Trigger
If a trigger is no longer required, use DROP TRIGGER.
Syntax:
DROP TRIGGER trigger_name;
For example:
DROP TRIGGER after_employee_insert;
This permanently removes the specified trigger from the database.
Advantages of MySQL Triggers
Triggers can provide several benefits.
1. Automatic Execution
The database automatically executes the trigger when its associated event occurs.
2. Data Validation
Triggers can help enforce certain data rules before information is stored.
3. Audit Logging
They can automatically record changes to important data.
4. Reduced Application-Side Logic
Some database-level operations can be handled without writing additional application code.
5. Consistency
Triggers can help ensure that specific database actions happen consistently whenever the triggering event occurs.
Disadvantages of MySQL Triggers
Triggers should be used carefully because they also have potential drawbacks.
1. Hidden Logic
A developer running an INSERT, UPDATE, or DELETE statement may not immediately realize that additional operations are being performed by triggers.
2. Debugging Can Become Difficult
When several triggers and database operations interact, finding the source of an unexpected change can become harder.
3. Performance Considerations
Triggers add additional database work to the triggering operation. Complex trigger logic can therefore affect performance.
4. Maintenance
A database containing many triggers can become more difficult to understand and maintain.
Conclusion
MySQL triggers are powerful database objects that allow developers to automatically execute SQL operations when specific table events occur.
They can respond to INSERT, UPDATE, and DELETE events and can execute either BEFORE or AFTER the associated operation.
With features such as OLD and NEW, triggers can inspect previous and incoming values, making them useful for auditing, validation, data consistency, and automated database operations.
However, triggers should be designed carefully. Keeping them simple, well-documented, and easy to maintain can help prevent unexpected database behavior.
If you're learning MySQL, understanding triggers is an important step toward working with more advanced database concepts.
Top comments (0)