Trigger in Mysql

create table employee (id INT AUTO_INCREMENT PRIMARY KEY, name varchar(255), employeeNumber varchar(255), createdDate datetime);

create table employee_audit (id INT AUTO_INCREMENT PRIMARY KEY,employee_id int,  name varchar(255), employeeNumber varchar(255), createdDate datetime,audit_created_date datetime);

Trigger:

delimiter //

create TRIGGER employee_update After update on employee  for each row  begin  insert into employee_audit (employee_id,name,employeeNumber,createdDate,audit_created_date) values (OLD.id,OLD.name,OLD.employeeNumber,OLD.createdDate,sysdate());  end;//

delimiter ;

in above statement we can see delimiter // this is used to change the delimiter ; to // we need to change delimiter because we are using multiple ; in trigger so after changing delimiter we can use // to break the statement

insert into employee (name,employeeNumber,createddate) values('sushil','001',sysdate);

update employee set name = 'sushil mittal' where id = 1;

//after update new entry get created in employee_audit table with exist record

we can add condition in trigger also like:

if we want to audit only if empployeeNumber get updated

 IF :OLD.employeeNumber != :NEW.employeeNumber THEN

delimiter //

create TRIGGER employee_update After update on employee  for each row  begin 

 IF :OLD.employeeNumber != :NEW.employeeNumber THEN

 insert into employee_audit (employee_id,name,employeeNumber,createdDate,audit_created_date) values (OLD.id,OLD.name,OLD.employeeNumber,OLD.createdDate,sysdate());  end;//

delimiter ;