为了账号安全,请及时绑定邮箱和手机立即绑定

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

标签:
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, UPDATEASBEGINUPDATE 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 ENDGO 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 = CASEWHEN possibility*severity>=9 THEN 3 WHEN possibility*severity <9 AND possibility*severity>=4 THEN 2 WHEN possibility*severity <4 THEN 1 ENDWHERE id = new.id; && CREATE TRIGGER trg_risks_update AFTER UPDATE ON `projectrisk` FOR EACH ROW UPDATE projectrisk SET classification = CASEWHEN possibility*severity>=9 THEN 3 WHEN possibility*severity <9 AND possibility*severity>=4 THEN 2 WHEN possibility*severity <4 THEN 1 ENDWHERE 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 BEGININSERT 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 ROWBEGININSERT 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 ;


点击查看更多内容
TA 点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
  • 推荐
  • 评论
  • 收藏
  • 共同学习,写下你的评论
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消