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=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 # 删除 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=4 then 2 when possibility*severity=9 then 3 when 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=4 then 2 when new.possibility*new.severity=9 then 3 when 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 ;
以上就是mysql触发器运用于迁移和同步数据的实例教程_mysql的内容。