Mysql必读MySQL触发器运用于迁移和同步数据的实例教程

《Mysql必读MySQL触发器运用于迁移和同步数据的实例教程》要点：
本文介绍了Mysql必读MySQL触发器运用于迁移和同步数据的实例教程，希望对您有用。如果有疑问，可以联系我们。

1.迁移数据
进行数据库移植,SQL Server=>MySQL.SQL Server上有如下的Trigger
MYSQL实例

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的语法.MYSQL实例

# 创建 
CREATE TRIGGER <触发器名称> 
{ BEFORE | AFTER } 
{ INSERT | UPDATE | DELETE } 
ON <表名称> 
FOR EACH ROW 
<触发器SQL语句> 
 
# 删除 
DROP TRIGGER <触发器名称> 

注：创建触发器需要CREATE TRIGGER权限.(HeidiSQL中执行Trigger语句会有bug)MYSQL实例

由于MySQL中的每个触发器只能针对一个动作,所以本次移植就需要创建两个触发器.对于发生变更的行,在触发器中可以用 NEW 来代替.
下边的触发器有什么问题吗?MYSQL实例

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了.MYSQL实例

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.同步备份数据记录表
添加记录到新记录表
MYSQL实例

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,new.BerthStartTime);
END ;

?mysql触发器监控mysql数据表记录删除操作 DELIMITER $$MYSQL实例

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 ;

删除前 添加原记录备份到另一记录表
MYSQL实例

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,BerthStartTime)
     VALUES
(old.AlarmId,old.AlarmS
tatus,old.ADDTIME,old.Bert
hStartTime);
     
  END;
$$
DELIMITER ;

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!