Triggers in MySQL

Trigger MySQL: Trigger can be used in two ways with before and after with Delete ,insert and update command .Let me show you a example of before update  . I have created two table employee and employee_audit . employee table is table where my raw data will be stored and employee_audit table is table where to store data after changes or can say trigger execution .

Table structure of employee and employee_audit table :

CREATE TABLE  `test`.`employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `lstname` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

CREATE TABLE  `test`.`employee_audit` (
`id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `lstname` varchar(20) DEFAULT NULL,
  `action` varchar(50) DEFAULT NULL,
  `upd_datetime` datetime DEFAULT NULL,
  `newname` varchar(20) DEFAULT NULL,
  `newlstname` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

Trigger Creation :

DELIMITER $$
CREATE TRIGGER before_employee_update2
    BEFORE UPDATE ON employee
    FOR EACH ROW
BEGIN
    INSERT INTO employee_audit
    SET action = 'update',
     name = OLD.name,
     lstname = OLD.lstname,
     upd_datetime = now(),
     newname = NEW.name ,
     newlstname = new.lstname ;
END$$
DELIMITER ;

Let us suppose ,we have insert data into employee table :

So before execute any update ,we will see ,first employee_audit table data .

Now I am executing a command update on name Gaurav :

update employee set name = 'guddu' where name = 'gaurav' ;

Check name has been changed ,now  we will see our employee_audit table content :

So you can ,what ever ever ,we have defined in trigger ,accordingly things are executing and updateded employee_audit table.

You can see trigger detail by just executing command “show triggers”.