How to use MySQL triggers to migrate and synchronize data,

How to use MySQL triggers to migrate and synchronize data,

1. migrate data
Port the database, SQL Server => MySQL. SQL Server has the following triggers

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 

I have briefly understood the Trigger syntax in MySQL.

# Create trigger <TRIGGER Name> {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON <Table Name> for each row <trigger SQL statement> # DELETE DROP TRIGGER <TRIGGER name>

Note: The create trigger permission is required to CREATE a TRIGGER. (A bug occurs when the Trigger statement is executed in HeidiSQL)

Since each trigger in MySQL can only be used for one action, you need to create two triggers for this migration. You can use NEW in the trigger to replace the changed rows.
Is there any problem with the trigger below?

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 ; 

The problem is that changes in the trigger will trigger the trigger and enter an endless loop. After the following changes are made, the system is 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. Synchronize the backup data record table
Add record to new record table

DELIMITER $$USE `DB_Test`$$CREATE  /*!50017 DEFINER = 'root'@'%' */  TRIGGER `InsertOPM_Alarm_trigger` BEFORE INSERT ON `OPM_Alarm`   FOR EACH ROW BEGININSERT 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 ROWBEGIN 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 trigger monitoring mysql Data Table Record deletion operation 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 ;

Back up the original record to another record table before deletion

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.AlarmStatus,old.AlarmHandleUser,         old.AlarmHandleTime,old.ADDTIME,old.ParkUserId,old.BerthCode,old.BargainOrderCode,old.BerthStartTime);       END;$$DELIMITER ;

Articles you may be interested in:

• MySQL trigger basic learning tutorial
• Tutorials on basic functions and triggers in MySQL stored procedures
• Mysql trigger for data synchronization between two tables