How to Create After Insert Trigger in MySQL: Syntax and Example

To create an AFTER INSERT trigger in MySQL, use the CREATE TRIGGER statement followed by the trigger name, timing (AFTER), event (INSERT), and the table name. Inside the trigger body, write the SQL statements to execute after a new row is inserted.

Syntax

The basic syntax to create an AFTER INSERT trigger in MySQL is:

• CREATE TRIGGER trigger_name: Names your trigger.
• AFTER INSERT ON table_name: Specifies the trigger runs after a new row is inserted into table_name.
• FOR EACH ROW: Runs the trigger for every inserted row.
• BEGIN ... END: Contains the SQL statements to execute.

sql

CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
  -- SQL statements here
END;

Example

This example creates an AFTER INSERT trigger that logs each new user added to a users table into a user_log table.

sql

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE user_log (
  log_id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  action VARCHAR(50),
  action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_log(user_id, action) VALUES (NEW.id, 'User added');
END$$
DELIMITER ;

-- Insert a new user to test the trigger
INSERT INTO users(name) VALUES ('Alice');

-- Check the user_log table
SELECT * FROM user_log;

Output

log_id | user_id | action     | action_time
-------|---------|------------|---------------------
1      | 1       | User added | 2024-06-01 12:00:00

Common Pitfalls

Common mistakes when creating AFTER INSERT triggers include:

• Forgetting to change the DELIMITER before and after the trigger definition, causing syntax errors.
• Using BEFORE INSERT syntax when you want actions after insertion.
• Trying to modify the same table that fired the trigger, which can cause recursion errors.
• Not using NEW.column_name to access inserted row values.

sql

/* Wrong: Missing DELIMITER change causes error */
CREATE TRIGGER after_insert_wrong
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_log(user_id, action) VALUES (NEW.id, 'Added');
END;

/* Right: Use DELIMITER to define trigger properly */
DELIMITER $$
CREATE TRIGGER after_insert_right
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_log(user_id, action) VALUES (NEW.id, 'Added');
END$$
DELIMITER ;

Quick Reference

Remember these key points when creating AFTER INSERT triggers in MySQL:

Concept	Description
CREATE TRIGGER	Starts trigger creation with a unique name
AFTER INSERT ON table_name	Runs trigger after a new row is inserted
FOR EACH ROW	Executes trigger for every inserted row
NEW.column_name	Accesses the inserted row's column values
DELIMITER $$ ... $$ DELIMITER ;	Changes delimiter to define trigger body

Key Takeaways

Use CREATE TRIGGER with AFTER INSERT to run code after new rows are added.

Always change DELIMITER before and after trigger definition to avoid syntax errors.

Use NEW.column_name to access values of the inserted row inside the trigger.

Avoid modifying the same table that fires the trigger to prevent recursion.

Test triggers by inserting rows and checking expected side effects.