服务器之家

服务器之家 > 正文

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

时间：2020-05-26 15:32     来源/作者：秦朝古月

1.迁移数据
进行数据库移植，SQL Server=>MySQL。SQL Server上有如下的Trigger

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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的语法。

?
1
2
3
4
5
6
7
8
9
10
# 创建
CREATE TRIGGER <触发器名称>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名称>
FOR EACH ROW
<触发器SQL语句>
 
# 删除
DROP TRIGGER <触发器名称>

注：创建触发器需要CREATE TRIGGER权限。（HeidiSQL中执行Trigger语句会有bug）

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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了。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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.同步备份数据记录表
添加记录到新记录表

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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 $$

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 ;

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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 触发器 同步数据 

相关文章

热门资讯

歪歪漫画vip账号共享2020_yy漫画免费账号密码共享
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享 2020-04-07
沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意
沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意 2019-07-07
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
男生常说24816是什么意思？女生说13579是什么意思？
男生常说24816是什么意思？女生说13579是什么意思？ 2019-09-17
超A是什么意思 你好a表达的是什么
超A是什么意思 你好a表达的是什么 2019-06-06
返回顶部