How to Create Trigger in MySQL: Syntax and Example

In MySQL, you create a trigger using the CREATE TRIGGER statement followed by the trigger name, timing (BEFORE or AFTER), event (INSERT, UPDATE, or DELETE), and the action to perform. The trigger runs automatically when the specified event happens on the table.

Syntax

The basic syntax to create a trigger in MySQL is:

• CREATE TRIGGER trigger_name: Names the trigger.
• BEFORE or AFTER: Defines when the trigger runs relative to the event.
• INSERT, UPDATE, or DELETE: Specifies the event that activates the trigger.
• ON table_name: The table the trigger is attached to.
• FOR EACH ROW: Runs the trigger for each affected row.
• BEGIN ... END: Contains the SQL statements to execute.

sql

CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name
FOR EACH ROW
BEGIN
  -- SQL statements
END;

Example

This example creates a trigger that logs a message into a log_table after a new row is inserted into employees. It shows how to use AFTER INSERT and access the new row's data with NEW.column_name.

sql

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

CREATE TABLE log_table (
  log_id INT PRIMARY KEY AUTO_INCREMENT,
  message VARCHAR(255),
  log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO log_table(message) VALUES (CONCAT('New employee added: ', NEW.name));
END$$
DELIMITER ;

-- Insert a new employee to test the trigger
INSERT INTO employees(name, position) VALUES ('Alice', 'Developer');

-- Check the log_table
SELECT * FROM log_table;

Output

log_id | message                  | log_time
-------|--------------------------|---------------------
1      | New employee added: Alice| 2024-06-01 12:00:00

Common Pitfalls

Common mistakes when creating triggers include:

• Forgetting to set the DELIMITER when defining multi-statement triggers, causing syntax errors.
• Using OLD and NEW incorrectly; NEW is for INSERT and UPDATE, OLD is for UPDATE and DELETE.
• Creating triggers that cause infinite loops by modifying the same table they are triggered on.
• Not specifying FOR EACH ROW, which is required for row-level triggers.

sql

/* Wrong: Missing DELIMITER change causes error */
CREATE TRIGGER wrong_trigger
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO log_table(message) VALUES ('Test');
END;

/* Right: Use DELIMITER to define trigger */
DELIMITER $$
CREATE TRIGGER correct_trigger
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO log_table(message) VALUES ('Test');
END$$
DELIMITER ;

Quick Reference

Part	Description
CREATE TRIGGER trigger_name	Defines the trigger's name
BEFORE | AFTER	When the trigger runs relative to the event
INSERT | UPDATE | DELETE	The event that activates the trigger
ON table_name	Table the trigger is attached to
FOR EACH ROW	Runs the trigger for each affected row
BEGIN ... END	SQL statements executed by the trigger

Key Takeaways

Use CREATE TRIGGER with BEFORE or AFTER and the event to define when the trigger runs.

Always set DELIMITER when creating triggers with multiple statements.

Use NEW and OLD to access row data inside triggers correctly.

Avoid triggers that modify the same table to prevent infinite loops.

FOR EACH ROW is required to run the trigger for every affected row.