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