[MySQL]-execution error #1442

Source: http://hi.baidu.com/loveyurui/blog/item/1c657ac9131b9a15bf09e67d.html

 

+ ---- + ------ +
| ID | Sal | num |
+ ---- + ------ +
| 13 | 600 | 10 |
| 14 | 200 | 10 |
| 15 | 300 | 10 |
+ ---- + ------ +

 

The compiled MySQL trigger is as follows:

Create trigger ins_trig before insert on hello. yy
For each row
Begin
Update YY set sal = Sal + 100 Where num> 10
End;

This trigger can be successfully executed and then tested (activated)

Insert into YY values (, 11 );

Theoretically, according to the trigger definition, the inserted value should be 16 2100 11, but the following is a depressing error:

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.

I searched for all the solutions on the Internet, and finally found solution on a foreign post.

Change the trigger definition to the following:

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;

Then insert the test value.

Mysql> insert into YY values (, 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)

original post connection: 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/