[MySQL] – 執行錯誤 #1442

來源：http://hi.baidu.com/loveyurui/blog/item/1c657ac9131b9a15bf09e67d.html

 

+----+------+------+
| 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/