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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL触发器运用于迁移和同步数据的实例教程_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4365字，纯文字阅读大概需要7分钟。

内容图文

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=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 
 
 
# 删除 
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=4 THEN 2 
WHEN possibility*severity=9 THEN 3 
WHEN 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=4 THEN 2 
 WHEN new.possibility*new.severity=9 THEN 3 
 WHEN 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的内容，更多相关内容请关注PHP中文网（www.gxlcms.com）！

内容总结

以上是互联网集市为您收集整理的MySQL触发器运用于迁移和同步数据的实例教程_MySQL全部内容，希望文章能够帮你解决MySQL触发器运用于迁移和同步数据的实例教程_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端