Create Trigger in MySQL

 Create a 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 the 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;

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
);

Next, create a BEFORE UPDATE the 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();

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;

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;

Finally, query the employees_audit table to check if the trigger was fired by the UPDATE statement:

SELECT * FROM employees_audit;

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.