Many a times, we have come across a need that execution of DML statement on one database table should lead to modification in another table with some specific set of values. Whats the novice way to do? Simple, fire the first modification query then write relevant second modification query and fire it. Simple isn't it?

But this approach has more cons then pros. The very first one that comes is, what if second query to be fired has values which may vary depending on what was modified in first one. Just imagine keeping track of all possible queries. Then next one that stands up is in case the queries are result of some action on any application. The application will hit the database twice, once for first query and if it is successful then for second query resulting in increased to and fro traffic.

So, what is the solution ? The database object that comes to help us here is TRIGGERS. First, just a brief intro on what are triggers. Trigger is nothing but a procedure that gets auto executed when a database event for which it is defined, occurs.

Lets see the basic syntax for a trigger. For better understanding we will break the command into parts

1. How to create ? As it is also a database object its created using CREATE command

   CREATE TRIGGER  <TRIGGER_NAME> 

2. When should be it be fired i.e whether before or after the responsible event ?

   BEFORE|AFTER

3. What event should be responsible to fire this trigger?

   INSERT|UPDATE|DELETE

4. On which table the event is to be checked to fire the trigger ?

   on <TABLE_NAME>

5. In case of trigger is to be executed at row level i.e once for every row modification use the following :

   FOR EACH ROW
   

6. What should the trigger do

   BEGIN
   <trigger body>
   END;
   

Lets recap this in an example.
We want to capture when the salaries were incremented and by how much amount. Instead of writing any complex logic or writing two insert statements We will create a trigger which will insert into the increments table the required information.

Create a table employee_master to store employee details

CREATE TABLE `employee_master` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `date_of_birth` datetime DEFAULT NULL,
  `salary` decimal(8,2) DEFAULT '6000.00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Create another table to keep track of increments

CREATE TABLE `employee_increments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) DEFAULT NULL,
  `date_of_increment` TIMESTAMP,
  `increment` decimal(8,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Insert some data in employee_master

insert  into `employee_master`(`id`,`name`,`date_of_birth`,`salary`) values (1,'John','1968-05-20 00:00:00','7000.00');
insert  into `employee_master`(`id`,`name`,`date_of_birth`,`salary`) values (2,'Peter','1978-06-24 00:00:00','6000.00');
insert  into `employee_master`(`id`,`name`,`date_of_birth`,`salary`) values (3,'Smith','1972-05-30 00:00:00','8000.00');

Now the trigger

DELIMITER $$

create trigger `track_increments` AFTER UPDATE on `employee_master` 
for each row BEGIN
insert into employee_increments set employee_id = new.id,increment  = new.salary - old.salary;
END;
$$

DELIMITER ;

To test the above trigger execute the following command :

update employee_master set salary = 10000

The trigger will be fired once for every row updated and the resulting entries in employee_increments will look like

id   employee_id   date_of_increment   increment
1      1          2015-04-03 20:48:14   3000.00
2      2          2015-04-03 20:48:14   4000.00
3      3          2015-04-03 20:48:14   2000.00

Few things to remember :

1. If the trigger is already created and needs to be altered execute first DROP command

    DROP TRIGGER <TRIGGER_NAME>;
   

   So the code will look like

   DELIMITER $$
   
   DROP TRIGGER `employee`.`track_increments`$$
   
   create trigger `track_increments` AFTER UPDATE on `employee_master` 
   for each row BEGIN
   insert into employee_increments set employee_id = new.id,increment  = new.salary - old.salary;
   END;
   $$
   
   DELIMITER ;
   

2. What is DELIMITER used for ? Default delimiter is

    ; 

   We need to use this in triggers when we must define multiple statements so, we need an alternative delimiter to define the end of trigger. This is achieved by changing the default by using DELIMITER command as in above code snippet. Most common used is $$