1.遷移數(shù)據(jù)
進(jìn)行數(shù)據(jù)庫移植，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的語法。
| # 創(chuàng)建 CREATE TRIGGER <觸發(fā)器名稱> { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON <表名稱> FOR EACH ROW <觸發(fā)器SQL語句> # 刪除 DROP TRIGGER <觸發(fā)器名稱> |
注：創(chuàng)建觸發(fā)器需要CREATE TRIGGER權(quán)限。（HeidiSQL中執(zhí)行Trigger語句會(huì)有bug）
由于MySQL中的每個(gè)觸發(fā)器只能針對一個(gè)動(dòng)作，所以本次移植就需要?jiǎng)?chuàng)建兩個(gè)觸發(fā)器。對于發(fā)生變更的行，在觸發(fā)器中可以用 NEW 來代替。
下邊的觸發(fā)器有什么問題嗎？
| 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 ; |
問題就是，沒有考慮到觸發(fā)器中的修改也會(huì)觸發(fā)觸發(fā)器，進(jìn)入了死循環(huán)。做了如下修改后，終于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.同步備份數(shù)據(jù)記錄表
添加記錄到新記錄表
| 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 ; |
