mysql error 1442_ERROR 1442 (HY000): Can't update table '...' in stored function/trigger because it ...

一、ＭySQL错误现象：MySQL执行创建的触发器时，报如下错误：ERROR 1442 (HY000): Can't update table 'tmp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

下面为错误触发器的创建过程：

1、创建测试表格：drop table if exists tmp;

create table tmp (id int, n1 int, n2 int);

insert tmp values(1, 10, 50);

MariaDB [test]> select * from tmp;

+------+------+------+

| id | n1 | n2 |

+------+------+------+

| 1 | 10 | 50 |

+------+------+------+

1 row in set (0.01 sec)

2、创建一个MySQL触发器：DELIMITER $

drop trigger if exists tmp_update$

create trigger tmp_update

after update on tmp

for each row

begin

update tmp set n2=n1*5 where id=new.id;

end$

DELIMITER ;

３、测试触发效果：mysql> select * from tmp;

+------+------+------+

| id | n1 | n2 |

+------+------+------+

| 1 | 10 | 50 |

+------+------+------+

1 row in set (0.00 sec)

mysql> update tmp set n1=2 where id=1;

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

二、错误原因：　　这是由于MySQL触发器，触发事件的表与触发更新的表是用一表。触发更新同一表时，不应该使用update语句更新，应该使用set修改NEW对象更新即可。

三、解决方案：1、将触发器的创建SQL语句改成如下并执行创建即可：DELIMITER $

drop trigger if exists tmp_update$

create trigger tmp_update

before update on tmp -- 更新之前执行，这样才能在NEW插入到数据库之前，修改NEW.n2

for each row

begin

set new.n2 = new.n1*5; -- 直接修改new.n2

end$

DELIMITER ; 触发器测试效果：aiezu.com> select * from tmp;

+------+------+------+

| id | n1 | n2 |

+------+------+------+

| 1 | 10 | 50 |

+------+------+------+

1 row in set (0.00 sec)

aiezu.com> update tmp set n1=12 where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

aiezu.com> select * from tmp;

+------+------+------+

| id | n1 | n2 |

+------+------+------+

| 1 | 12 | 60 |

+------+------+------+

1 row in set (0.00 sec)