MySQL触发器运用于迁移和同步数据的实例教程
1.迁移数据
进行数据库移植，SQLServer=>MySQL。SQLServer上有如下的Trigger
SETQUOTED_IDENTIFIERON GO SETANSI_NULLSON GO ALTERTRIGGER[trg_risks]ONdbo.projectrisk FORINSERT,UPDATE AS BEGIN UPDATEprojectrisk SETclassification= case whencalc>=9then3 whencalc<9andcalc>=4then2 whencalc<4then1 end from(selectinserted.id,inserted.possibility*inserted.severityascalcfrominserted)asT1 whereprojectrisk.id=T1.id END GO SETQUOTED_IDENTIFIEROFF GO SETANSI_NULLSON GO
简单了解了下MySQL中，Trigger的语法。
#创建
CREATETRIGGER<触发器名称>
{BEFORE|AFTER}
{INSERT|UPDATE|DELETE}
ON<表名称>
FOREACHROW
<触发器SQL语句>
#删除
DROPTRIGGER<触发器名称>
注：创建触发器需要CREATETRIGGER权限。（HeidiSQL中执行Trigger语句会有bug）
由于MySQL中的每个触发器只能针对一个动作，所以本次移植就需要创建两个触发器。对于发生变更的行，在触发器中可以用NEW来代替。
下边的触发器有什么问题吗？
delimiter&& CREATETRIGGERtrg_risks_insert AFTERINSERTON`projectrisk` FOREACHROW UPDATEprojectriskSETclassification=CASE WHENpossibility*severity>=9THEN3 WHENpossibility*severity<9ANDpossibility*severity>=4THEN2 WHENpossibility*severity<4THEN1 END WHEREid=new.id; && CREATETRIGGERtrg_risks_update AFTERUPDATEON`projectrisk` FOREACHROW UPDATEprojectriskSETclassification=CASE WHENpossibility*severity>=9THEN3 WHENpossibility*severity<9ANDpossibility*severity>=4THEN2 WHENpossibility*severity<4THEN1 END WHEREid=new.id; && delimiter;
问题就是，没有考虑到触发器中的修改也会触发触发器，进入了死循环。做了如下修改后，终于OK了。
delimiter&& CREATETRIGGERtrg_risks_insert BEFOREINSERTON`projectrisk` FOREACHROW BEGIN SETnew.classification=CASE WHENnew.possibility*new.severity>=9THEN3 WHENnew.possibility*new.severity<9ANDnew.possibility*new.severity>=4THEN2 WHENnew.possibility*new.severity<4THEN1 END; END && CREATETRIGGERtrg_risks_update BEFOREUPDATEON`projectrisk` FOREACHROW BEGIN SETnew.classification=CASE WHENnew.possibility*new.severity>=9THEN3 WHENnew.possibility*new.severity<9ANDnew.possibility*new.severity>=4THEN2 WHENnew.possibility*new.severity<4THEN1 END; END && delimiter;
2.同步备份数据记录表
添加记录到新记录表
DELIMITER$$ USE`DB_Test`$$ CREATE /*!50017DEFINER='root'@'%'*/ TRIGGER`InsertOPM_Alarm_trigger`BEFOREINSERTON`OPM_Alarm` FOREACHROWBEGIN INSERTINTOOPM_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; CREATETRIGGERInsertOPM_Alarm_trigger BEFOREINSERTONOPM_Alarm FOREACHROW BEGIN INSERTINTOOPM_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`$$ DROPTRIGGER/*!50032IFEXISTS*/`SYS_OPM_trigger`$$ CREATE /*!50017DEFINER='root'@'%'*/ TRIGGER`SYS_OPM_trigger`AFTERDELETEON`OPM_Alarm` FOREACHROWBEGIN DECLAREstrVARCHAR(40000); SETstr=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); INSERTINTOOPM_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`$$ DROPTRIGGER/*!50032IFEXISTS*/`InsertOPM_Alarm_trigger`$$ CREATE /*!50017DEFINER='root'@'%'*/ TRIGGER`InsertOPM_Alarm_trigger`BEFORE DELETEON`OPM_Alarm` FOREACHROWBEGIN INSERTINTOOPM_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;