Mysql触发器

需求：要在不同物业下自增长id

创建tir_homeinfo触发器，

DELIMITER $
create trigger tri_homeinfo after insert
on homeinfo for each row
begin
set @t :=(select ifnull(max(home_id),0) from homeinfo where pro_id=new.pro_id);
update homeinfo set home_id = @t where pro_id=new.pro_id;
end$
DELIMITER ;

 函数说明

• ifnull（x，y）：当x为null时候返回y
• set @t定义用户变量赋值可以使用‘=’或是‘：=’都可以。
• delimiter 修改结束符

mysql> insert into homeinfo value (null,1,2);
ERROR 1442 (HY000): Can't update table 'homeinfo' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

提交后发现出现问题，网上说自身调用自身的原因，要将update改为set。

修改如下：

DELIMITER $
create trigger tri_homeinfo after insert
on homeinfo for each row
begin
set @t :=(select ifnull(max(home_id),0) from homeinfo where pro_id=new.pro_id);
set new.home_id=@t+1;
end$
DELIMITER ;

但是运行时候还是发现了问题

ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger
mysql> DELIMITER ;

在这发现了解决方案

https://www.chenyudong.com/archives/database-trigger-new-old-value-understand.html

将after修改为before

DELIMITER $
create trigger tri_homeinfo before insert
on homeinfo for each row
begin
set @t :=(select ifnull(max(home_id),0) from homeinfo where pro_id=new.pro_id);
set new.home_id=@t+1;
end$
DELIMITER ;

mysql> select * from homeinfo order by pro_id;
+-----+---------+--------+
| _id | home_id | pro_id |
+-----+---------+--------+
|  13 |       1 |      1 |
|  14 |       2 |      1 |
|  15 |       3 |      1 |
|  10 |       1 |      2 |
|  11 |       2 |      2 |
|  12 |       3 |      2 |
+-----+---------+--------+
6 rows in set (0.00 sec)

发现成功，完事开头难！

转载于:https://my.oschina.net/u/2951890/blog/1586777