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, 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 ;