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.