mysql - 在 mysql 中使用多行触发器将两列合并为一列

Question

以下是我的表架构:

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| LicenceID    | int(11)      | NO   | PRI | NULL    |       | 
| PassingRTO   | varchar(4)   | NO   |     | NULL    |       | 
| DLNO         | int(15)      | YES  |     | NULL    |       | 
| DateOfIssue  | date         | NO   |     | NULL    |       | 
| DateOfExpiry | date         | NO   |     | NULL    |       | 
| COV          | varchar(6)   | NO   |     | NULL    |       | 
| DateOfBirth  | date         | NO   |     | NULL    |       | 
| BloodGroup   | varchar(3)   | YES  |     | NULL    |       | 
| FullName     | varchar(50)  | NO   |     | NULL    |       | 
| FathersName  | varchar(50)  | YES  |     | NULL    |       | 
| Address      | varchar(150) | NO   |     | NULL    |       | 
| PinCode      | int(6)       | NO   |     | NULL    |       | 
| IssuingAuth  | int(7)       | NO   |     | NULL    |       | 
| IDIA         | int(11)      | YES  |     | NULL    |       | 
| Valid        | tinyint(4)   | NO   |     | NULL    |       | 
+--------------+--------------+------+-----+---------+-------+

我想做的是当我插入一个新行时，我希望我的 DLNO 作为 PassingRTO+LicenceID 和 IDIA 作为 PassingRTO+IssuingAuth。

我尝试了同样的使用 -

create trigger insert_combined 
after insert on LicencesDB 
for each row 
BEGIN 
set new.IDIA = concat(new.PassingRTO, new.IssuingAuth);
set new.DLNO = concat(new.PassingRTO,new.LicenceID); 
END;

但是给了我一个错误——

ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger ERROR 1193 (HY000): Unknown system variable 'DLNO' ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1

现在我有两个问题，我们可以在触发器中使用多行吗？我们不能在创建表格时合并两列吗？比如 col1 = col2 + col3？

提前致谢!

Answer 1

不要忘记更改 DELIMITER

DELIMITER $$
create trigger insert_combined 
after insert on LicencesDB 
for each row 
BEGIN 
set new.IDIA = concat(new.PassingRTO, new.IssuingAuth);
set new.DLNO = concat(new.PassingRTO,new.LicenceID); 
END $$
DELIMITER ;