神剑山庄资源网 Design By www.hcban.com
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 ;
神剑山庄资源网 Design By www.hcban.com
神剑山庄资源网
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件!
如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
神剑山庄资源网 Design By www.hcban.com
暂无MySQL触发器运用于迁移和同步数据的实例教程的评论...
RTX 5090要首发 性能要翻倍!三星展示GDDR7显存
三星在GTC上展示了专为下一代游戏GPU设计的GDDR7内存。
首次推出的GDDR7内存模块密度为16GB,每个模块容量为2GB。其速度预设为32 Gbps(PAM3),但也可以降至28 Gbps,以提高产量和初始阶段的整体性能和成本效益。
据三星表示,GDDR7内存的能效将提高20%,同时工作电压仅为1.1V,低于标准的1.2V。通过采用更新的封装材料和优化的电路设计,使得在高速运行时的发热量降低,GDDR7的热阻比GDDR6降低了70%。
更新日志
2024年10月19日
2024年10月19日
- 群星《歌手2024 第4期》[FLAC/分轨][563.76MB]
- RiffKitten-ChaosParade(2024)[24-44,1]FLAC
- RuneKlakegg-Nattevandrer(2024)[24-96]\FLAC
- 赖冰霞.2006-金嗓典藏辑2IN1(南方金点系列)2CD【南方】【WAV+CUE】
- 群星.1988-国语金曲尽精英VOL.2【瑞成】【WAV+CUE】
- 张宇.1999-雨一直下【EMI百代】【WAV+CUE】
- 袁娅维.2021-月亮失眠了(DELUXE)【华纳】【FLAC分轨】
- AlanBroadbent-JazzFunk(2024)[24-44,1]
- 梅艳芳《绝代芳华》开盘母带[低速原抓WAV+CUE]
- ABC唱片-《外国精逊母带直刻神奇黑胶[APE+CUE].
- 群星《天赐的声音第五季 第6期》[320K/MP3][101.43MB]
- 群星《天赐的声音第五季 第6期》[FLAC/分轨][526.16MB]
- 群星《说唱梦工厂 第2期》[320K/MP3][96.37MB]
- 杨宗宪.1994-留你留袂着【有容唱片】【WAV+CUE】
- 李翊君.2005-勇敢的爱【创意唱片】【WAV+CUE】