BEFORE & AFTER Triggers
In this page:
BEFORE vs AFTER Triggers
BEFORE triggers execute prior to MySQL actually writing a row to disk, giving them a chance to inspect or change the data first, while AFTER triggers run only once the change has already been permanently saved.
Example: BEFORE vs AFTER Triggers
DELIMITER //
CREATE TRIGGER before_insert_user BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SET NEW.email = LOWER(NEW.email);
END //
CREATE TRIGGER after_insert_user AFTER INSERT ON users
FOR EACH ROW
BEGIN
INSERT INTO user_audit (user_id, action) VALUES (NEW.id, 'created');
END //
DELIMITER ;Using BEFORE Triggers to Clean Data
Because a BEFORE trigger runs ahead of the write, it's the right place to validate or reformat incoming data — using the NEW keyword, you can adjust a value before it ever gets stored.
Example: Using BEFORE Triggers to Clean Data
DELIMITER //
CREATE TRIGGER trim_name BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SET NEW.name = TRIM(NEW.name);
END //
DELIMITER ;Accessing OLD Values in UPDATE Triggers
UPDATE triggers have access to both OLD and NEW at once, letting you directly compare what a row looked like before the change against what it looks like after, which is essential for building audit logs.
Example: Accessing OLD Values in UPDATE Triggers
DELIMITER //
CREATE TRIGGER log_email_change BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
IF OLD.email <> NEW.email THEN
INSERT INTO user_audit (user_id, action) VALUES (OLD.id, CONCAT('email changed from ', OLD.email, ' to ', NEW.email));
END IF;
END //
DELIMITER ;BEFORE DELETE Triggers
A BEFORE DELETE trigger runs just before a row disappears, and since the row still exists at that moment, the OLD keyword lets you capture and log its details before they're gone for good.
Example: BEFORE DELETE Triggers
DELIMITER //
CREATE TRIGGER log_deleted_user BEFORE DELETE ON users
FOR EACH ROW
BEGIN
INSERT INTO user_audit (user_id, action) VALUES (OLD.id, 'deleted');
END //
DELIMITER ;Verifying Audit Logs
Querying an audit table populated by BEFORE and AFTER triggers lets you review a full history of who changed what and when, without the application itself ever having to write that logging code.
Example: Verifying Audit Logs
CREATE TABLE user_audit (user_id INT, action TEXT);
INSERT INTO user_audit VALUES (1, 'created'), (1, 'deleted');
SELECT * FROM user_audit WHERE user_id = 1;Chapter Quiz — Complete all 3 topics to unlock
0/3 topics done
Complete these topics first: