Create the First Trigger in MySQL

Let’s start creating the first trigger in MySQL by following a simple scenario. In the sample databse, we have employees table as follows:

CREATE TABLE `employees` (
  `employeeNumber` int(11) NOT NULL,
  `lastName` varchar(50) NOT NULL,
  `firstName` varchar(50) NOT NULL,
  `extension` varchar(10) NOT NULL,
  `email` varchar(100) NOT NULL,
  `officeCode` varchar(10) NOT NULL,
  `reportsTo` int(11) default NULL,
  `jobTitle` varchar(50) NOT NULL,
  PRIMARY KEY  (`employeeNumber`)
)

Now you want to keep the changes of employee's data in another table whenever data of an employee's record changed. In order to do so you 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. 

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;


You can test the trigger which 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