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
|
简单了解了下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 = CASEWHEN possibility*severity>=9 THEN 3 WHEN possibility*severity
|
问题就是，没有考虑到触发器中的修改也会触发触发器，进入了死循环。做了如下修改后，终于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
|
2.同步备份数据记录表
添加记录到新记录表
?
DELIMITER $$USE `DB_Test`$$CREATE 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 `SYS_OPM_trigger`$$ CREATE 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 `InsertOPM_Alarm_trigger`$$ CREATE 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 ;
|
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/73/viewspace-2805849/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
PHP 异常处理
下一篇：
MySQL LEFT JOIN语句
请登录后发表评论
登录
全部评论
