CREATE TRIGGER
In this page:
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
-- statements using NEW.column / OLD.column
ENDWhat is a Trigger?
A trigger is a block of code stored in the database that fires automatically in response to a specific table event, running in reaction to an INSERT, UPDATE, or DELETE without the application ever calling it directly.
Example: What is a Trigger?
DELIMITER //
CREATE TRIGGER log_new_user AFTER INSERT ON users
FOR EACH ROW
BEGIN
INSERT INTO user_audit (user_id, action) VALUES (NEW.id, 'created');
END //
DELIMITER ;Creating an INSERT Trigger
An INSERT trigger runs automatically the moment a new row is added to the table it's attached to, either just before or just after that row is written, depending on how it's defined.
Example: Creating an INSERT Trigger
DELIMITER //
CREATE TRIGGER before_user_insert BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SET NEW.created_at = NOW();
END //
DELIMITER ;Accessing NEW Values
Inside an INSERT or UPDATE trigger, the NEW keyword gives you access to the actual values of the row being written, letting the trigger inspect or even adjust those values before they're saved.
Example: Accessing NEW Values
DELIMITER //
CREATE TRIGGER lowercase_email BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SET NEW.email = LOWER(NEW.email);
END //
DELIMITER ;Testing Trigger Execution
Triggers don't announce themselves when they run — there's no visible confirmation during the insert or update itself, so verifying one is working means querying whatever table the trigger is supposed to affect afterward.
Example: Testing Trigger Execution
CREATE TABLE users (id INT, email TEXT);
CREATE TABLE user_audit (user_id INT, action TEXT);
INSERT INTO users VALUES (1, '[email protected]');
-- No confirmation is printed -- check the audit table instead
SELECT * FROM user_audit;Trigger Restrictions
Triggers can't return a result set directly back to whoever ran the original statement, and MySQL also blocks a trigger from modifying the very same table that caused it to fire, to avoid infinite recursive triggering.
Example: Trigger Restrictions
-- A trigger on users cannot modify users itself, only other tables,
-- to avoid infinite recursive triggering:
DELIMITER //
CREATE TRIGGER log_new_user AFTER INSERT ON users
FOR EACH ROW
BEGIN
INSERT INTO user_audit (user_id, action) VALUES (NEW.id, 'created');
END //
DELIMITER ;- Trying to change a row in an
AFTERtrigger withSET NEW.col, which is only allowed inBEFOREtriggers. - Forgetting
FOR EACH ROW, which is required in MySQL. - Using
OLDin anINSERTtrigger orNEWin aDELETEtrigger, where those rows do not exist.
Chapter Quiz — Complete all 3 topics to unlock
0/3 topics done
Complete these topics first: