Create Trigger in MySQL
Summary: in this tutorial, you will learn how to use the MySQL CREATE TRIGGER statement to create a trigger in the database.
Introduction to MySQL CREATE TRIGGER statement
The CREATE TRIGGER statement creates a new trigger. Here is the basic syntax of the CREATE TRIGGER statement:
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
trigger_body;Code language: SQL (Structured Query Language) (sql)
In this syntax:
- First, specify the name of the trigger that you want to create after the
CREATE TRIGGERkeywords. Note that the trigger name must be unique within a database. - Next, specify the trigger action time which can be either
BEFOREorAFTERwhich indicates that the trigger is invoked before or after each row is modified. - Then, specify the operation that activates the trigger, which can be
INSERT,UPDATE, orDELETE. - After that, specify the name of the table to which the trigger belongs after the
ONkeyword. - Finally, specify the statement to execute when the trigger activates. If you want to execute multiple statements, you use the
BEGIN ENDcompound statement.
The trigger body can access the values of the column being affected by the DML statement.
To distinguish between the value of the columns BEFORE and AFTER the DML has fired, you use the NEW and OLD modifiers.
For example, if you update the column description, in the trigger body, you can access the value of the description before the update OLD.description and the new value NEW.description.
The following table illustrates the availability of the OLD and NEW modifiers:
| Trigger Event | OLD | NEW |
INSERT | No | Yes |
UPDATE | Yes | Yes |
DELETE | Yes | No |
MySQL trigger examples
Let’s start creating a trigger in MySQL to log the changes of the employees table.
First, create a new table named employees_audit to keep the changes to the employees table:
CREATE TABLE employees_audit (
id INT AUTO_INCREMENT PRIMARY KEY,
employeeNumber INT NOT NULL,
lastname VARCHAR(50) NOT NULL,
changedat DATETIME DEFAULT NULL,
action VARCHAR(50) DEFAULT NULL
);Code language: SQL (Structured Query Language) (sql)
Next, create a BEFORE UPDATE trigger that is invoked before a change is made to the employees table.
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
INSERT INTO employees_audit
SET action = 'update',
employeeNumber = OLD.employeeNumber,
lastname = OLD.lastname,
changedat = NOW();Code language: SQL (Structured Query Language) (sql)
Inside the body of the trigger, we used the OLD keyword to access values of the columns employeeNumber and lastname of the row affected by the trigger.
Then, show all triggers in the current database by using the SHOW TRIGGERS statement:
SHOW TRIGGERS;Code language: SQL (Structured Query Language) (sql)
In addition, if you look at the schema using MySQL Workbench under the employees > triggers, you will see the before_employee_update trigger as shown in the screenshot below:
After that, update a row in the employees table:
UPDATE employees
SET
lastName = 'Phan'
WHERE
employeeNumber = 1056;Code language: SQL (Structured Query Language) (sql)
Finally, query the employees_audit table to check if the trigger was fired by the UPDATE statement:
SELECT * FROM employees_audit;Code language: SQL (Structured Query Language) (sql)
The following shows the output of the query:
As you see clearly from the output, the trigger was automatically invoked and inserted a new row into the employees_audit table.
In this tutorial, you have learned how to use the MySQL CREATE TRIGGER statement to create a new trigger in the database.