Post
Ask Question

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:
  1. CREATE TABLE employees (  
  2. id int(11) NOT NULL AUTO_INCREMENT,  
  3. employeeNumber int(11) NOT NULL,  
  4. lastname varchar(50) NOT NULL,  
  5. PRIMARY KEY (id)  
  6. )  
  7. CREATE TABLE employees_edit (  
  8. id int(11) NOT NULL AUTO_INCREMENT,  
  9. employeeNumber int(11) NOT NULL,  
  10. lastname varchar(50) NOT NULL,  
  11. changedon datetime DEFAULT NULL,  
  12. action varchar(50) DEFAULT NULL,  
  13. PRIMARY KEY (id)  
  14. )  
Here is the syntax of creating a trigger in MySQL. 
  1. CREATE  
  2. [DEFINER = { user | CURRENT_USER }]  
  3. TRIGGER trigger_name trigger_time trigger_event  
  4. 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. 
  1. DELIMITER $$  
  2. CREATE TRIGGER before_employee_update  
  3. BEFORE UPDATE  
  4. ON employees  
  5. FOR EACH ROW  
  6. BEGIN  
  7. INSERT INTO employees_edit  
  8. SET action = 'update',  
  9. employeeNumber = OLD.employeeNumber,  
  10. lastname = OLD.lastname,  
  11. changedon = NOW();  
  12. END$$  
  13. 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