Create a Trigger in MYSQL

This blog and SQL script explains how to create a trigger in MySQL.

A trigger is a database object usually SQL query that is set on a database table and fired on a certain event. Then an event occurs, the SQL query is executed. For example, a trigger can be set on when a new record is inserted into a table or a record is updated or deleted from a table. 

 

One example of trigger is, when a new blog is added to database, the blog count of the author is increased by one. In this case, we can create an INSERT trigger on the table and when a new record is inserted in the table, the blog count value is increased by one by using a SQL query.

 

Here is another example. If a new database record is updated into a table, the old record can be insereted into a history table to keep track of the changes. This can be done using a trigger.

 

The best part of using tiggers is, the query written as a trigger executes automatically. 

 

Let's look it how it works.

 

We will create two tables as following:

CREATE TABLE employees (
id int(11) NOT NULL AUTO_INCREMENT,
employeeNumber int(11) NOT NULL,
lastname varchar(50) NOT NULL,
PRIMARY KEY (id)
)
CREATE TABLE employees_edit (
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)
)

Here is the syntax of creating a trigger in MySQL. 

CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_body

Now we need to create a trigger, when we update the Employees table, it stores the updation in employees _edit table automatically. 

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

The above SQL query creates a trigger and inserts some data into Employees table and update it, a updation history will automatically save to the Employees_edit table with old data . If we need to fetch a old data from a specified date, we can easily fetch it from employees_edit table. 

 

Here are more details on triggers, Learn Everything About Tiggers In SQL Server