An example of MySQL trigger syntax

The function I want to implement is when I update a table. Query a record from three tables and insert it into another table. Here's how I write the trigger:

The first write triggers such as the following:

CREATE TRIGGER Istmingxi
After UPDATE on Sys_shenbao for each ROW BEGIN
SELECT @kid: =jl.kid, @gonghao: =shen.gonghao, @vuid: =jl.vuid, @zhibiao: =ct.leibie,
@citiao: =ct.bianhao, @fenshu: =ct.fenshu, @bumen: =zl.bumen, @ctime: =shen.verifytime
From Sys_shenbao Shen
INNER JOIN sys_shenbjl jl on jl.d = Shen.sid
INNER JOIN Sys_citiao ct on ct.bianhao = SHEN.CTBH
INNER JOIN Sys_jibzl ZL on Zl.gonghao = Shen.gonghao
WHERE shen.sid = New.sid;
If Fenshu > 0 Then
Set @jiafen = Fenshu;
Set @jianfen = 0;
Else
Set @jiafen = 0;
Set @jianfen = Fenshu;
End If;
INSERT into Sys_mingxi (' Kid ', ' Gonghao ', ' vuid ', ' Zhibiao ', ' bumen ', ' jiafen ', ' jianfen ', ' Yue ', ' CTime ')
VALUES (Kid,gonghao,vuid,zhibiao,citiao,bumen,jiafen,jianfen,0,ctime);
END;

generate the above trigger times error: notallowed to return a result set from a trigger; The reason is that trigger does not agree to return a select-out result set. So the "select @kid: =jl.kid ..." section changes to another way of writing, such as the following

CREATE TRIGGER Istmingxi
After UPDATE on Sys_shenbao for each ROW BEGIN
The SELECT jl.skid into @kid, the Shen.gonghao into @gonghao, the jl.vuid into @vuid, the Ct.leibie into @zhibiao,
Ct.bianhao into @citiao, Ct.fenshu to @fenshu, zl.bumen into @bumen, shen.verifytime to @ctime
From Sys_shenbao Shen
INNER JOIN sys_shenbjl jl on jl.id = Shen.sid
INNER JOIN Sys_citiao ct on ct.bianhao = SHEN.CTBH
INNER JOIN Sys_jibzl ZL on Zl.gonghao = Shen.gonghao
WHERE shen.sid = New.sid;
If Fenshu > 0 Then
Set @jiafen = Fenshu;
Set @jianfen = 0;
Else
Set @jiafen = 0;
Set @jianfen = Fenshu;
End If;
INSERT into Sys_mingxi (' Kid ', ' Gonghao ', ' vuid ', ' Zhibiao ', ' bumen ', ' jiafen ', ' jianfen ', ' Yue ', ' CTime ')
VALUES (Kid,gonghao,vuid,zhibiao,citiao,bumen,jiafen,jianfen,0,ctime);
END;

At this point will still error: SQL error [1327] [42000]: undeclared variable:shen; undeclared variable:shen; The reason is that the "select Jl.skic into @kid ..." section of the SELECT statement is syntactically incorrect.

The reasons are: to assign a value to multiple variables through a SELECT statement. Please use the following statement form: Select Id,name into @v1, @v2 from ...

So finally I changed it to the following way, for example:

CREATE TRIGGER Istmingxi
After UPDATE on Sys_shenbao for each ROW BEGIN
SELECT Jl.skid,shen.gonghao,jl.vuid,ct.leibie,ct.bianhao,ct.fenshu,zl.bumen,shen.verifytime
Into @kid, @gonghao, @vuid, @zhibiao, @citiao, @fenshu, @bumen, @ctime
From Sys_shenbao Shen
INNER JOIN sys_shenbjl jl on jl.id = Shen.sid
INNER JOIN Sys_citiao ct on ct.bianhao = SHEN.CTBH
INNER JOIN Sys_jibzl ZL on Zl.gonghao = Shen.gonghao
WHERE shen.sid = New.sid;
If Fenshu > 0 Then
Set @jiafen = Fenshu;
Set @jianfen = 0;
Else
Set @jiafen = 0;
Set @jianfen = Fenshu;
End If;
INSERT into Sys_mingxi (' Kid ', ' Gonghao ', ' vuid ', ' Zhibiao ', ' bumen ', ' jiafen ', ' jianfen ', ' Yue ', ' CTime ')
VALUES (Kid,gonghao,vuid,zhibiao,citiao,bumen,jiafen,jianfen,0,ctime);
END;

When you do this, you will get an error: ERROR 1172 (42000): Result consisted of more than one row.

The reason for this error is that select INTO returns multiple rows of records, which are not agreed when assigning a value to a variable, and then the problem of where condition is checked;

The problem of returning a record after the where condition has been changed has been overcome.

Repeat error: SQL error [1054] [42S22]: Unknown column ' Fenshu ' in ' Field list ', the reason for this is the following syntax error in reference variable "Fenshu";

The "@" symbol is used to define the variable above, and the @ symbol is used for the following reference.

The final trigger is changed to such as the following:

After UPDATE on Sys_shenbao for each ROW BEGIN
If New.verifystatus = 2 Then
SELECT Jl.skid,shen.gonghao,jl.vuid,ct.leibie,ct.bianhao,ct.fenshu,zl.bumen,shen.verifytime
Into @kid, @gonghao, @vuid, @zhibiao, @citiao, @fenshu, @bumen, @ctime
From Sys_shenbao Shen
INNER JOIN sys_shenbjl jl on jl.id = Shen.sid
INNER JOIN Sys_citiao ct on ct.bianhao = SHEN.CITBH
INNER JOIN Sys_jibzl ZL on Zl.gonghao = Shen.gonghao
WHERE shen.id = new.id;
If @fenshu > 0 Then
Set @jiafen = @fenshu;
Set @jianfen = 0;
Else
Set @jiafen = 0;
Set @jianfen = @fenshu;
End If;
INSERT into Sys_mingxi (' Kid ', ' Gonghao ', ' vuid ', ' Zhibiao ', ' Citiao ', ' bumen ', ' jiafen ', ' jianfen ', ' Yue ', ' CTime ')
VALUES (@kid, @gonghao, @vuid, @zhibiao, @citiao, @bumen, @jiafen, @jianfen, 0, @ctime);
End If;
END;

To this trigger succeeded.

The final summary is for example the following:

1,trigger do not agree to return the select out of the result set

2 . To assign values to multiple variables through a SELECT statement. Please use the following statement form: Select Id,name into @v1, @v2 from ...

3.SELECT INTO gives variable assignment syntax does not agree to return multiple lines

4, the definition of variables with the @ symbol, the following reference should also use the @ symbol

An example of MySQL trigger syntax