How To 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 TRIGGER keywords. Note that the trigger name must be unique within a database.
Next, specify the trigger action time which can be either BEFORE or AFTER which 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, or DELETE.
After that, specify the name of the table to which the trigger belongs after the ON keyword.
Finally, specify the statement to execute when the trigger activates. If you want to execute multiple statements, you use the BEGIN END compound 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 thebefore_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.

Was this tutorial helpful?