MySQL触发器运用于迁移和同步数据的实例教程

1.迁移数据
进行数据库移植，sql server=>mysql。sql server上有如下的trigger

set quoted_identifier on  
go 
set ansi_nulls on  
go 
alter trigger [trg_risks] on dbo.projectrisk 
for insert, update 
as 
begin 
update projectrisk 
  set classification = 
  case   
  when calc>= 9 then 3 
  when calc <9 and calc>=4 then 2 
  when calc <4 then 1 
  end  
  from (select inserted.id, inserted.possibility*inserted.severity as calc from inserted) as t1 
  where projectrisk.id = t1.id 
end 
go 
set quoted_identifier off  
go 
set ansi_nulls on  
go 

简单了解了下mysql中，trigger的语法。

# 创建 
create trigger <触发器名称> 
{ before | after } 
{ insert | update | delete } 
on <表名称> 
for each row 
<触发器sql语句> 
 
# 删除 
drop trigger <触发器名称> 

注：创建触发器需要create trigger权限。（heidisql中执行trigger语句会有bug）

由于mysql中的每个触发器只能针对一个动作，所以本次移植就需要创建两个触发器。对于发生变更的行，在触发器中可以用 new 来代替。
下边的触发器有什么问题吗？

delimiter && 
create trigger trg_risks_insert 
after insert on `projectrisk` 
for each row 
update projectrisk set classification = case 
when possibility*severity>=9 then 3 
when possibility*severity <9 and possibility*severity>=4 then 2 
when possibility*severity <4 then 1 
end 
where id = new.id; 
&& 
create trigger trg_risks_update 
after update on `projectrisk` 
for each row 
update projectrisk set classification = case 
when possibility*severity>=9 then 3 
when possibility*severity <9 and possibility*severity>=4 then 2 
when possibility*severity <4 then 1 
end 
where id = new.id; 
&& 
delimiter ; 

问题就是，没有考虑到触发器中的修改也会触发触发器，进入了死循环。做了如下修改后，终于ok了。

delimiter && 
create trigger trg_risks_insert 
before insert on `projectrisk` 
for each row 
begin 
 set new.classification = case 
 when new.possibility*new.severity>=9 then 3 
 when new.possibility*new.severity <9 and new.possibility*new.severity>=4 then 2 
 when new.possibility*new.severity <4 then 1 
 end; 
end 
&& 
create trigger trg_risks_update 
before update on `projectrisk` 
for each row 
begin 
 set new.classification = case 
 when new.possibility*new.severity>=9 then 3 
 when new.possibility*new.severity <9 and new.possibility*new.severity>=4 then 2 
 when new.possibility*new.severity <4 then 1 
 end; 
end 
&& 
delimiter ; 

2.同步备份数据记录表
添加记录到新记录表

delimiter $$
use `db_test`$$
create
  /*!50017 definer = 'root'@'%' */
  trigger `insertopm_alarm_trigger` before insert on `opm_alarm` 
  for each row begin
insert into opm_alarm_copy (alarmid,alarmcode,alarmtypeid,alarmlevelid,alarmobjectcode,alarmstatus,alarmhandleuser,
alarmhandletime,addtime,parkuserid,berthcode,bargainordercode,berthstarttime)
values(new.alarmid,new.alarmcode,new.alarmtypeid,new.alarmlevelid,new.alarmobjectcode,new.alarmstatus,new.alarmhandleuser,
new.alarmhandletime,new.addtime,new.parkuserid,new.berthcode,new.bargainordercode,new.berthstarttime);
  end;
$$
delimiter ;

create trigger insertopm_alarm_trigger 
 before insert on opm_alarm 
 for each row
begin 
insert into opm_alarm_copy (alarmid,alarmcode,alarmtypeid,alarmlevelid,alarmobjectcode,alarmstatus,alarmhandleuser,
alarmhandletime,addtime,parkuserid,berthcode,bargainordercode,berthstarttime)
values(new.alarmid,new.alarmcode,new.alarmtypeid,new.alarmlevelid,new.alarmobjectcode,new.alarmstatus,new.alarmhandleuser,
new.alarmhandletime,new.addtime,new.parkuserid,new.berthcode,new.bargainordercode,new.berthstarttime);
end ;

 mysql触发器监控mysql数据表记录删除操作 delimiter $$

use `db_test`$$

drop trigger /*!50032 if exists */ `sys_opm_trigger`$$

create
  /*!50017 definer = 'root'@'%' */
  trigger `sys_opm_trigger` after delete on `opm_alarm` 
  for each row begin
  declare str varchar(40000);
   set str=concat(old.alarmid,'@',old.alarmcode,'@',old.alarmtypeid,'@',old.alarmlevelid,'@',
   old.alarmobjectcode,'@',old.alarmstatus,'@',old.alarmhandleuser,'@',old.alarmhandletime,'@',
   old.addtime,'@',old.parkuserid,'@',old.berthcode,'@',old.bargainordercode,'@',old.berthstarttime);
   insert into opm_alarmaction_log(username,client_ip,delete_before_key,delete_date) 
  values(substring_index(user(),'@',1),substring_index(user(),'@',-1), str, now());
  end;
$$


delimiter ;

删除前 添加原记录备份到另一记录表

delimiter $$

use `db_test`$$

drop trigger /*!50032 if exists */ `insertopm_alarm_trigger`$$

create
  /*!50017 definer = 'root'@'%' */
  trigger `insertopm_alarm_trigger` before 

delete on `opm_alarm` 
  for each row begin
   insert into opm_alarm_copy 

(alarmid,alarmcode,alarmtypeid,alarmlevelid,alarmobjectcode,alarmstatus,alarmhandleuser,
    alarmhandletime,addtime,parkuserid,berthcode,bargainordercode,berthstarttime)
     values

(old.alarmid,old.alarmcode,old.alarmtypeid,old.alarmlevelid,old.alarmobjectcode,old.alarms

tatus,old.alarmhandleuser,
         

old.alarmhandletime,old.addtime,old.parkuserid,old.berthcode,old.bargainordercode,old.bert

hstarttime);
     

  end;
$$

delimiter ;