How to use MySQL triggers to migrate and synchronize data

This article describes how to use MySQL triggers to migrate and synchronize data. two examples of SQL Server Data Migration to MySQL and synchronous backup data table records are provided. For more information, see 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=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 {BEFORE | AFTER} {INSERT | UPDATE | DELETE} on for each row # DELETE DROP TRIGGER

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=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 ;

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=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. 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 ;

The above is the MySQL trigger used for data migration and synchronization instance tutorial _ MySQL content. For more information, please follow the PHP Chinese network (www.php1.cn )!