一个有关mySQL 触发器激活的错误 #1442

最新推荐文章于 2022-11-14 21:00:00 发布

laker_bbs

最新推荐文章于 2022-11-14 21:00:00 发布

阅读量904

点赞数

文章标签： mysql insert table database each 测试

本文链接：https://blog.csdn.net/laker_bbs/article/details/6474914

版权

database:hello table :yy

+----+------+------+
| Id | sal | num |
+----+------+------+
| 13 | 600 |   10 |
| 14 | 200 |   10 |
| 15 | 300 |   10 |
+----+------+------+

写好的MySQL触发器如下：

CREATE TRIGGER ins_trig before insert ON hello.yy
    FOR EACH ROW
    BEGIN
    update yy set sal=sal+100 where num>10
    END;

这个触发器是可以执行成功的然后我们测试(激活)

insert into yy values(0,2000,11);

理论上按照触发器的定义插入的值应该为16 2100 11 可是令人郁闷的错误出现了：

ERROR 1442 (HY000): Can't update table 'yy' in stored function/trigger because i
t is already used by statement which invoked this stored function/trigger.

网上找遍了所有的解决办法最后在国外的一个帖子上找到了solution

将触发器定义改成如下：

CREATE TRIGGER ins_trig before insert ON hello.yy
    FOR EACH ROW
    BEGIN
    if new.num>10 then
        set new.sal = new.sal + 100;
    end if;
    END;

然后插入刚才的测试值就ok了

mysql> insert into yy values(0,2000,11);
Query OK, 1 row affected (0.01 sec)

mysql> select * from yy;
+----+------+------+
| Id | sal | num |
+----+------+------+
| 13 | 600 |   10 |
| 14 | 200 |   10 |
| 15 | 300 |   10 |
| 16 | 2100 |   11 |
+----+------+------+
5 rows in set (0.00 sec)

原帖连接为：http://crazytoon.com/2008/03/03/mysql-error-1442-hy000-cant-update-table-t1-in-stored-functiontrigger-because-it-is-already-used-by-statement-which-invoked-this-stored-functiontrigger/

看完了这片文章相信你也有收获吧，那就在帮我个忙 http://www.shb-china.com/huodong/huahu/2010-01-20/1844.html 投票支持下啦