Jun 2nd, 2014 at 06:26 PM

Creating "insert", "update" and "delete" triggers on table in MySQL

In the following code you have triggers au_sales,bd_sales,ai_sales which are logs(write) into log_sales table automatically for each of every operations (insert, update, delete) on the sales table. Assuming that you have a sales table having id,employee_id, revenue, trans_on fields. sales_triggers.sql

drop table if exists log_sales;
create table log_sales(
 id int(10) primary key auto_increment,
 activity varchar(20),
 description text,
 worked_on datetime
);

delimiter //

drop trigger if exists au_sales//
create trigger au_sales after update on sales
for each row
begin
  insert into log_sales(activity,description,worked_on)values('UPDATE',concat('Employee: ',old.employee_id,'-

>',new.employee_id,' Revenue: ',old.revenue,'->',new.revenue),now());
end;//

drop trigger if exists bd_sales//
create trigger bd_sales before delete on sales
for each row
begin
  insert into log_sales(activity,description,worked_on)values('DELETE',concat('Employee: ',old.employee_id,'-

>','None',' Revenue: ',old.revenue,'->','None'),now());
end;//

drop trigger if exists ai_sales//
create trigger ai_sales after insert on sales
for each row
begin
  insert into log_sales(activity,description,worked_on)values('INSERT',concat('Employee: ','None','-

>',new.employee_id,' Revenue: ','None','->',new.revenue),now());
end;//

delimiter ;

New save this file to d:sales_triggers.sql and import this file to mysql as follows:

mysql>source d:sales_triggers.sql

Now make some insert, update and delete operations on the sales table:

mysql>update sales set employee_id='100',revenue=400 where id=101;
mysql>insert into sales(employee_id,revenue,trans_on)values('101',500,now());
mysql>delete from sales where id=1;

Now view log_sales table to examine if your triggers are working fine.

mysql>select * from log_sales;