Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to create a trigger in MySQL

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 516
    Comment on it

    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 $$

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: