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 clas
<p>1.迁移数据进行数据库移植，SQL Server=>MySQL。SQL Server上有如下的Trigger </p>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 possibilityseverity>=9 THEN 3
WHEN possibilityseverity <9 AND possibilityseverity>=4 THEN 2
WHEN possibilityseverity <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 possibilityseverity>=9 THEN 3
WHEN possibilityseverity <9 AND possibilityseverity>=4 THEN 2
WHEN possibilityseverity <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.possibilitynew.severity>=9 THEN 3
WHEN new.possibilitynew.severity <9 AND new.possibilitynew.severity>=4 THEN 2
WHEN new.possibilitynew.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.possibilitynew.severity>=9 THEN 3
WHEN new.possibilitynew.severity <9 AND new.possibilitynew.severity>=4 THEN 2
WHEN new.possibilitynew.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 ;
