mysql 触发器 随机数_Mysql触发器 - Star0_0Sky的个人空间 - OSCHINA - 中文开源技术交流社区...

需求：要在不同物业下自增长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)

发现成功，完事开头难！