欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-02-23 12:08:16
1.迁移数据 进行数据库移植,sql server=>mysql。sql server上有如下的trigger set quoted_identif...

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 ;