Course: MySQL Training Course
Topic: Creating BEFORE UPDATE and AFTER UPDATE Triggers
Course Link: MySQL Training Course at JustAcademy
Demo Link: Register for a Course Demo
General Website: JustAcademy
MySQL triggers are database objects that execute automatically when a specified event occurs on a table. An UPDATE trigger runs when an existing row is modified using an SQL UPDATE statement. Triggers are useful for enforcing data rules, maintaining audit records, updating timestamps, tracking changes, and preserving historical information.
MySQL supports two main trigger timings for UPDATE operations:
For example, when an employee's salary changes, a BEFORE UPDATE trigger can validate the new salary, while an AFTER UPDATE trigger can save the previous and new salary in a history table.
After completing this topic, you should be able to:
A trigger is a named database object associated with a table. MySQL automatically invokes it when the specified table event occurs. A trigger is not normally called directly by a user like a stored procedure; it runs as part of the triggering SQL statement.
| Event | Purpose | Example |
|---|---|---|
| INSERT | Runs when a new row is inserted. | Set a default status or record a new entry. |
| UPDATE | Runs when an existing row is updated. | Track a salary or address change. |
| DELETE | Runs when a row is deleted. | Record deleted data in an audit table. |
A BEFORE UPDATE trigger executes before each affected row is updated. It can inspect the existing values and the proposed new values. It can also assign values to fields through the NEW pseudorecord before the row is stored.
CREATE TRIGGER trigger_name
BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN
-- Validation or value adjustment statements
END;The exact body depends on the database rules you want to enforce. Use a BEFORE UPDATE trigger when you need to examine or adjust the proposed row before the update is stored.
An AFTER UPDATE trigger executes after the row has been updated successfully. It is appropriate for actions that depend on the completed change, such as writing an audit entry or storing a previous value in a history table.
CREATE TRIGGER trigger_name
AFTER UPDATE ON table_name
FOR EACH ROW
BEGIN
-- Audit or related database statements
END;An AFTER UPDATE trigger cannot use NEW to modify the row that has just been updated. If a value must be adjusted before it is stored, use a BEFORE UPDATE trigger instead.
| Feature | BEFORE UPDATE | AFTER UPDATE |
|---|---|---|
| Execution time | Before the row is updated. | After the row is updated successfully. |
| Read old values | Yes, using OLD. | Yes, using OLD. |
| Read proposed new values | Yes, using NEW. | Yes, using NEW. |
| Change the row's NEW values | Yes, by assigning to NEW columns where permitted. | No, the updated row cannot be modified through NEW. |
| Typical purpose | Validation and adjustment. | Audit logging and history recording. |
| Example | Set updated_at before saving. | Store old and new salary values. |
In a row-level trigger, OLD and NEW refer to the row's values before and after the triggering operation. For UPDATE triggers, both are available.
| Keyword | Meaning in UPDATE Trigger | Example |
|---|---|---|
| OLD.column_name | The column value before the update. | OLD.salary |
| NEW.column_name | The proposed or resulting column value. | NEW.salary |
Suppose an employee's salary changes from 40000 to 45000. In the trigger, OLD.salary represents 40000 and NEW.salary represents 45000.
IF OLD.salary <> NEW.salary THEN
-- The salary value has changed
END IF;Be careful when comparing nullable values. If either value may be NULL, a comparison using <> can evaluate to NULL instead of TRUE or FALSE. MySQL's NULL-safe equality operator, <=>, can help:
IF NOT (OLD.salary <=> NEW.salary) THEN
-- The values differ, including NULL-to-value changes
END IF;Before creating triggers, create a database and a sample employees table. The following example uses an employee ID, name, salary, department, and modification timestamp.
CREATE DATABASE company_db;
USE company_db;
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
salary DECIMAL(10,2) NOT NULL,
department VARCHAR(50),
updated_at TIMESTAMP NULL DEFAULT NULL
);
INSERT INTO employees
(employee_id, employee_name, salary, department, updated_at)
VALUES
(1, 'Aarav Sharma', 40000.00, 'IT', NULL),
(2, 'Priya Verma', 50000.00, 'HR', NULL),
(3, 'Rahul Mehta', 45000.00, 'Finance', NULL);
SELECT * FROM employees;The table provides the starting data for the examples in the following sections.
Let's create a trigger that sets the updated_at column to the current date and time whenever an employee record is updated. This timestamp is assigned before the row is stored.
In MySQL command-line clients, change the statement delimiter temporarily because the trigger body contains semicolons.
DELIMITER $$
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
SET NEW.updated_at = CURRENT_TIMESTAMP;
END$$
DELIMITER ;DELIMITER $$ changes the command-line client's statement terminator so semicolons inside the trigger body are not treated as the end of the CREATE TRIGGER statement.CREATE TRIGGER before_employee_update creates a trigger with this name.BEFORE UPDATE ON employees specifies when and where the trigger runs.FOR EACH ROW means the trigger executes for each row affected by an UPDATE statement.SET NEW.updated_at = CURRENT_TIMESTAMP assigns the current timestamp to the proposed row.DELIMITER ; restores the usual delimiter in the command-line client.DELIMITER is a client command used by tools such as the MySQL command-line client. It is not part of the SQL statement sent to the MySQL server in the same way as CREATE TRIGGER.
UPDATE employees
SET salary = 42000.00
WHERE employee_id = 1;
SELECT employee_id, employee_name, salary, updated_at
FROM employees
WHERE employee_id = 1;The employee's salary should be updated to 42000.00, and updated_at should contain the timestamp assigned by the trigger.
BEFORE UPDATE triggers can also enforce selected business rules. For example, an organization may want to prevent an employee salary from being set to zero or a negative value.
DELIMITER $$
CREATE TRIGGER before_salary_validation
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF NEW.salary <= 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary must be greater than zero';
END IF;
END$$
DELIMITER ;The trigger raises a custom error when the proposed salary is zero or negative. The UPDATE statement then fails rather than storing that invalid salary.
-- Valid update
UPDATE employees
SET salary = 46000.00
WHERE employee_id = 1;
-- Invalid update: expected to raise an error
UPDATE employees
SET salary = -1000.00
WHERE employee_id = 1;The first statement should succeed if no other constraint or trigger prevents it. The second should be rejected by the validation trigger.
Validation triggers are useful when rules must be enforced for updates from multiple applications. However, database constraints such as CHECK, NOT NULL, UNIQUE, and foreign key constraints should also be used where suitable. For straightforward rules such as requiring salary to be positive, a CHECK constraint can often be simpler and more explicit.
An audit table stores a record of changes made to important data. It may contain the employee ID, previous salary, new salary, previous department, new department, and the time of the change.
CREATE TABLE employee_audit (
audit_id BIGINT AUTO_INCREMENT PRIMARY KEY,
employee_id INT NOT NULL,
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
old_department VARCHAR(50),
new_department VARCHAR(50),
changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);The audit_id column uniquely identifies each audit entry. The employee_id identifies the employee whose record changed. The old and new columns preserve the values before and after the update.
Now create an AFTER UPDATE trigger that records an audit row when an employee's salary or department changes. The trigger compares OLD and NEW values and inserts a record only when one of those fields differs.
DELIMITER $$
CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF NOT (OLD.salary <=> NEW.salary)
OR NOT (OLD.department <=> NEW.department) THEN
INSERT INTO employee_audit (
employee_id,
old_salary,
new_salary,
old_department,
new_department,
changed_at
)
VALUES (
OLD.employee_id,
OLD.salary,
NEW.salary,
OLD.department,
NEW.department,
CURRENT_TIMESTAMP
);
END IF;
END$$
DELIMITER ;UPDATE employees
SET salary = 48000.00,
department = 'Technology'
WHERE employee_id = 1;
SELECT *
FROM employee_audit
WHERE employee_id = 1
ORDER BY audit_id;The audit table should contain a record of the previous and new salary and department for the update. If the row is updated again with different salary or department values, another audit record should be added.
This example combines a BEFORE UPDATE trigger that sets a timestamp with an AFTER UPDATE trigger that records changes in an audit table.
CREATE TABLE employees_demo (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
salary DECIMAL(10,2) NOT NULL,
department VARCHAR(50),
updated_at TIMESTAMP NULL DEFAULT NULL
);
CREATE TABLE employee_demo_audit (
audit_id BIGINT AUTO_INCREMENT PRIMARY KEY,
employee_id INT NOT NULL,
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
old_department VARCHAR(50),
new_department VARCHAR(50),
changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO employees_demo
(employee_id, employee_name, salary, department)
VALUES
(101, 'Neha Kapoor', 55000.00, 'Marketing'),
(102, 'Vikram Singh', 60000.00, 'Operations');DELIMITER $$
CREATE TRIGGER before_employees_demo_update
BEFORE UPDATE ON employees_demo
FOR EACH ROW
BEGIN
SET NEW.updated_at = CURRENT_TIMESTAMP;
END$$
DELIMITER ;DELIMITER $$
CREATE TRIGGER after_employees_demo_update
AFTER UPDATE ON employees_demo
FOR EACH ROW
BEGIN
IF NOT (OLD.salary <=> NEW.salary)
OR NOT (OLD.department <=> NEW.department) THEN
INSERT INTO employee_demo_audit (
employee_id,
old_salary,
new_salary,
old_department,
new_department,
changed_at
)
VALUES (
OLD.employee_id,
OLD.salary,
NEW.salary,
OLD.department,
NEW.department,
CURRENT_TIMESTAMP
);
END IF;
END$$
DELIMITER ;UPDATE employees_demo
SET salary = 62000.00,
department = 'Business Operations'
WHERE employee_id = 101;
SELECT * FROM employees_demo
WHERE employee_id = 101;
SELECT * FROM employee_demo_audit
WHERE employee_id = 101
ORDER BY audit_id;The first SELECT shows the updated employee record, including the modification timestamp. The second SELECT shows the audit entry created for the salary or department change.
MySQL triggers are row-level triggers. If one UPDATE statement affects multiple rows, the trigger executes separately for each affected row. For example:
UPDATE employees
SET department = 'General'
WHERE salary < 50000.00;If three rows match the WHERE condition, the BEFORE UPDATE trigger runs for each of those rows, and the AFTER UPDATE trigger also runs for each successfully updated row. The audit trigger can therefore insert multiple history records.
Always test UPDATE statements carefully. An UPDATE statement without a WHERE clause may affect every row in the table, which can result in many trigger executions and audit entries.
SHOW TRIGGERS;SHOW TRIGGERS LIKE 'employees';The LIKE pattern filters trigger names, so it is not a reliable way to filter by the table name in every situation. To inspect triggers associated with a particular table, query INFORMATION_SCHEMA:
SELECT
TRIGGER_NAME,
EVENT_MANIPULATION,
ACTION_TIMING,
EVENT_OBJECT_TABLE,
ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
AND EVENT_OBJECT_TABLE = 'employees';SHOW CREATE TRIGGER before_employee_update;This statement displays the definition of a particular trigger, subject to the user's database privileges.
DROP TRIGGER IF EXISTS before_employee_update;
DROP TRIGGER IF EXISTS before_salary_validation;
DROP TRIGGER IF EXISTS after_employee_update;Dropping a trigger removes that trigger definition. It does not automatically delete existing records from the associated table or audit table.
| Problem | Possible Cause | Solution |
|---|---|---|
| Syntax error near END | Incorrect delimiter handling or missing semicolon. | Use DELIMITER in a supported client and check BEGIN/END syntax. |
| Trigger already exists | A trigger with the same name is already defined. | Choose another name or drop the existing trigger before recreating it. |
| Invalid salary is stored | No validation rule exists or the trigger is not attached to the expected table. | Inspect the trigger definition and test the rule with invalid input. |
| Audit entry is missing | The audited columns did not change, the trigger was not created, or the update failed. | Check trigger status, compare OLD and NEW values, and inspect the UPDATE result. |
| Unexpected duplicate audit entries | Multiple updates or multiple triggers are writing audit records. | Review application statements and inspect all triggers attached to the table. |
| Cannot modify NEW in AFTER trigger | AFTER triggers cannot assign to the updated row through NEW. | Move the adjustment to a BEFORE trigger. |
| NULL comparison behaves unexpectedly | Ordinary comparisons with NULL can return NULL. | Use NULL-safe equality with <=> when appropriate. |
| Trigger creation fails due to permissions | The database user lacks required privileges. | Ask the database administrator to grant the appropriate privileges. |
Triggers execute automatically, so their behavior can be less visible to developers who only inspect the application code. Keep trigger definitions documented and include them in database change management.
Every affected row can cause trigger logic to run. Complex calculations, unnecessary queries, and large audit inserts can slow down bulk updates. Keep the trigger body efficient and index audit tables according to how records will be searched.
An audit table can help explain what changed, but the exact audit information depends on the trigger's design. The example above records only salary and department changes; it does not record every column or identify the application user. If user identity is required, design a trustworthy approach for passing and validating that information.
With transactional tables such as InnoDB, an error in a trigger can cause the triggering statement to fail, and the changes made by the statement and its triggers are rolled back together. Trigger behavior also depends on the storage engines involved. Avoid mixing transactional assumptions with nontransactional tables without testing the consequences.
A MySQL trigger is a database object that executes automatically when a configured event occurs on a table.
It executes before an affected row is updated and can validate or adjust proposed values through NEW.
It executes after an affected row has been updated successfully and is often used to record audit information.
OLD refers to the previous row values, while NEW refers to the proposed or resulting row values in the trigger.
Yes. A BEFORE UPDATE trigger can assign a value to an eligible NEW column before the row is stored.
No. An AFTER trigger cannot change the row through NEW because the update has already taken place.
It specifies that the trigger body runs once for each row affected by the triggering statement.
In clients such as the MySQL command-line client, DELIMITER temporarily changes the statement terminator so the semicolons inside a multi-statement trigger body do not prematurely end the CREATE TRIGGER command.
A trigger can use SIGNAL SQLSTATE '45000' to raise a user-defined error when a business rule is violated.
It can insert OLD values, NEW values, a record identifier, and a timestamp into a separate audit table after the update succeeds.
The trigger runs separately for each affected row, so an audit trigger may create one audit record per qualifying row.
Use SHOW TRIGGERS or query INFORMATION_SCHEMA.TRIGGERS for detailed metadata.
Use DROP TRIGGER IF EXISTS trigger_name.
Comparisons involving NULL can return NULL rather than TRUE or FALSE. The NULL-safe equality operator <=> is useful when checking whether two values differ.
No. Use database constraints for appropriate integrity rules and triggers for logic that genuinely requires trigger behavior, such as custom auditing or more involved validation.
BEFORE UPDATE and AFTER UPDATE triggers help enforce database rules and maintain reliable records of changes. BEFORE UPDATE triggers are best suited to validating or adjusting values before they are stored. AFTER UPDATE triggers are useful for recording old and new values in audit or history tables after an update succeeds.
By understanding OLD and NEW, using NULL-safe comparisons where necessary, keeping trigger logic focused, and testing both single-row and multi-row updates, you can build maintainable MySQL solutions for employee management, inventory systems, customer records, and other applications.
Explore the course: MySQL Training Course
Book a demo: Register for a Course Demo
Visit JustAcademy: www.justacademy.co
