mysql触发器异常处理

MySQL触发器异常处理

MySQL触发器更新本表数据异常:Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this

如果你在触发器里面对刚刚插入的数据进行了 insert/update, 则出现这个问题。因为会造

成循环的调用.

create trigger test

before update on test

for each row

update test set NEW.updateTime = NOW() where id=NEW.ID;

END

应该使用set操作，而不是在触发器里使用 update,比如

create trigger test

before update on test

for each row

set NEW.updateTime = NOW();

END

MySQL触发器执行复合语句

触发器1.自增表中商品分类编码值+1，添加商品分类前给商品分类编码属性赋其对应的自

增表中的值

CREATE TRIGGER trigger1 BEFORE INSERT

ON dw_base_commodity_category_info

FOR EACH ROW

BEGIN

UPDATE sequence SET current_value=current_value+1 WHERE

`name`='dw_base_commodity_category_info';

SET NEW.code=(SELECT current_value FROM sequence WHERE

`name`='dw_base_commodity_category_info');

END;

删除触发器1：DROP TRIGGER trigger1;

触发器2.自增表中商品编码值+1，添加商品前给商品编码属性赋其对应的自增表中的值

CREATE TRIGGER trigger2 BEFORE INSERT

ON dw_commodity_fact_info

FOR EACH ROW

BEGIN

UPDATE sequence SET current_value=current_value+1 WHERE

`name`='dw_commodity_fact_info';

SET https://www.360docs.net/doc/1316253590.html,odity_code=(SELECT current_value FROM sequence WHERE `name`='dw_commodity_fact_info');

END;

删除触发器2：DROP TRIGGER trigger2;

查看所有触发器SQL:SELECT * FROM information_schema.`TRIGGERS`;