Mysql triggers apply to migrating and synchronizing instances of data tutorial _mysql

1. Migrating data
for database porting, SQL server=>mysql. The following trigger are available on SQL Server

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 
  while 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 in  
go 

A simple understanding of the next MySQL, trigger syntax.

# Create 
TRIGGER < trigger name > 
{before | After} 
{INSERT | UPDATE | Delete} on 
< table name > for each 
ROW 
< trigger SQL statement > 
 
# Delete 
drop TRIGGER < trigger name > 

Note: Creating trigger permission is required to create a trigger. (There will be bugs executing trigger statements in Heidisql)

Since each trigger in MySQL can only be targeted at one action, this migration requires the creation of two triggers. For a changed row, you can replace it with NEW in the trigger.
Is there a problem with the trigger below?

Delimiter && 
CREATE TRIGGER Trg_risks_insert after 
inserts 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 in ' 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 not taking into account the modification in the trigger triggers the trigger and goes into the dead loop. After making the following changes, finally 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 in ' 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 Backup data record table
add a record to a new record table

DELIMITER $$ use ' db_test ' $$ CREATE/*!50017 definer = ' root ' @ '% '/TRIGGER ' Insertopm_alarm_trigger ' On ' Opm_alarm ' for each ROW BEGIN inserts into Opm_alarm_copy (alarmid,alarmcode,alarmtypeid,alarmlevelid,alarmobjectcod
E,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 inserts into Opm_alarm_copy (alar Mid,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 monitor MySQL datasheet record delete operation DELIMITER $$

Use ' db_test ' $$

DROP TRIGGER/*!50032 IF EXISTS/' Sys_opm_trigger ' $$

CREATE
  /*!50017 definer = ' root ' @ '% ' * /
  TRIGGER ' Sys_opm_trigger ' after DELETE in ' 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;

Add the original record back to another record table before deleting

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 BEG
   in 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;