给表创建触发器,实现每5行数据序列号自动增长1

[dami_sky]

-- drop trigger TR_TB_ZJJL;
create TRIGGER TR_TB_ZJJL
AFTER INSERT OR DELETE ON tb_zjjl
FOR EACH ROW
BEGIN
update tb_zjjl set pzbh=(
select
concat(bd.mc,'-',case when qs.qh <10 then concat(0,qs.qh) else qs.qh end ,'-',CONCAT(gclqd.zjh,'00'),'-', '001') -- 动态增长 从 001 开始
from tb_zjjl zjjl
inner join tb_gclqd gclqd on zjjl.gclqd_ID=gclqd.ID
inner join tb_bd bd on bd.ID=gclqd.bd_ID
inner join tb_qs qs on qs.bd_ID=bd.ID
-- where zjjl.id='1'
-- and qs.ID='1' 此处应该写成 group by qs.ID
) WHERE id = new.id ;
END;
-- 测试
select
concat(bd.mc,'-',case when qs.qh <10 then concat(0,qs.qh) else qs.qh end ,'-',CONCAT(gclqd.zjh,'00'),'-', '001') as pzbh
from tb_zjjl zjjl
inner join tb_gclqd gclqd on zjjl.gclqd_ID=gclqd.ID
inner join tb_bd bd on bd.ID=gclqd.bd_ID
inner join tb_qs qs on qs.bd_ID=bd.ID
where zjjl.id='1'
and qs.ID='1'
;
/*******************/
+Q:2115063954 备注:CSDN-MySQL
/*******************/