Create a Trigger in MYSQL

Create Two tables as following :

CREATE TABLE employees (

id int(11) NOT NULL AUTO_INCREMENT,

employeeNumber int(11) NOT NULL,

lastname varchar(50) NOT NULL,

PRIMARY KEY (id)

)

 

CREATE TABLE employees_edit (

id int(11) NOT NULL AUTO_INCREMENT,

employeeNumber int(11) NOT NULL,

lastname varchar(50) NOT NULL,

changedon datetime DEFAULT NULL,

action varchar(50) DEFAULT NULL,

PRIMARY KEY (id)

)

Syntax of Trigger in MYSQL---

CREATE

[DEFINER = { user | CURRENT_USER }]

TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_body

now we need to create a trigger , when we update employees table, it store the updation in employees _edit table automatically.

DELIMITER $$

CREATE TRIGGER before_employee_update

BEFORE UPDATE

ON employees

FOR EACH ROW

BEGIN

INSERT INTO employees_edit

SET action = 'update',

employeeNumber = OLD.employeeNumber,

lastname = OLD.lastname,

changedon = NOW();

END$$

DELIMITER ;

Insert some data into employees table and update it, a updation history will automatically save in 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.