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 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 ROWBEGIN 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.AlarmStatus,old.AlarmHandleUser,         old.AlarmHandleTime,old.ADDTIME,old.ParkUserId,old.BerthCode,old.BargainOrderCode,old.BerthStartTime);       END;$$DELIMITER ;