login

Subscribe to MySQL Tutorial website RSS Feed
 » MySQL Triggers » Create the First Trigger in MySQL

Create the First Trigger in MySQL

Let’s start creating the first trigger in MySQL by following a simple scenario. In the sample database, we have employees table as follows:
Each time a employee wants change his/her last name, you want to keep track all of changes in another table. In order to do so you can create a new table called employees_audit  to keep track the changes.

 CREATE TABLE employees_audit ( 
 id int(11) NOT NULL AUTO_INCREMENT, 
 employeeNumber int(11) NOT NULL, 
 lastname varchar(50) NOT NULL, 
 changedon datetime DEFAULT NULL, 
 action varchar(50) DEFAULT NULL, 
 PRIMARY KEY (id) 
 )

In order to keep track the changes of last name of employee we can create a trigger that is fired before we make any update on the employees table.  Here is the source code of the trigger

 DELIMITER $$
 CREATE TRIGGER before_employee_update 
 BEFORE UPDATE ON employees
 FOR EACH ROW BEGIN
 INSERT INTO employees_audit
 SET action = 'update',
 employeeNumber = OLD.employeeNumber,
 lastname = OLD.lastname,
 changedon = NOW(); END$$
 DELIMITER ;

Now you can test the trigger you’ve created by updating last name of any employee in employees table. Suppose we update last name of employee which has employee number is 3:

 UPDATE employees
 SET lastName = 'Phan'
 WHERE employeeNumber = 1056

Now when you can see the changes audited automatically in the employees_audit table by executing the following query

 SELECT * 
 FROM employees_audit

In order to create a trigger you use the following syntax:

 CREATE TRIGGER trigger_name trigger_time trigger_event
 ON table_name
 FOR EACH ROW
 BEGIN
 ...
 END
  • CREATE TRIGGER statement is used to create triggers.
  • The trigger name should follow the naming convention [trigger time]_[table name]_[trigger event], for example before_employees_update
  • Trigger activation time can be BEFORE or AFTER. You must specify the activation time when you define a trigger.  You use BEFORE when you want to process action prior to the change being made in the table and AFTER if you need to process action after changes are made.
  • Trigger event can be INSERT, UPDATE and DELETE. These events cause trigger to fire and process logic inside trigger body. A trigger only can fire with one event. To define trigger which are fired by multiple events, you have to define multiple triggers, one for each event. Be noted that any SQL statements make update data in database table will cause trigger to fire. For example, LOAD DATA statement insert records into a table will also cause the trigger associated with that table to fire.
  • A trigger must be associated with a specific table. Without a table trigger does not exist so you have to specify the table name after the ON keyword.
  • You can write the logic between BEGIN and END block of the trigger.
  • MySQL gives you OLD and NEW keyword to help you write trigger more efficient. The OLD keyword refers to the existing row before you update data and the NEW keyword refers to the new row after you update data.

In this tutorial, you have learnt how to create the first trigger. In this example, you wrote the first trigger to audit changes of last name of employee in employees table.