程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL觸發器運用於遷移和同步數據的實例教程,

MySQL觸發器運用於遷移和同步數據的實例教程,

編輯: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 ;

您可能感興趣的文章:

  • MySQL中觸發器的基礎學習教程
  • MySQL存儲過程中的基本函數和觸發器的相關學習教程
  • mysql 觸發器實現兩個表的數據同步

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved