Bug #47008 Error 1442: mysql
Submitted: 31 Aug 2009 8:56 Modified: 31 Aug 2009 9:25
Reporter: rajesh goswami Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.41 OS:Linux
Assigned to: CPU Architecture:Any

[31 Aug 2009 8:56] rajesh goswami
Description:
I am facing error 1442 when create trigger as below

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

How to repeat:
DELIMITER $$
CREATE TRIGGER trigger1 AFTER UPDATE ON EMP
FOR EACH ROW 
DELETE FROM EMP WHERE DESG='MANAGER' AND SAL>15000;
END $$
DELIMITER ;

update from emp set desg='abc';

1442 (HY000): Can’t update table ‘t1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
[31 Aug 2009 9:04] Valeriy Kravchuk
This is a documented limitation. Please, read http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html:

"Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger."
[31 Aug 2009 9:25] rajesh goswami
Thanks, Valeriy Kravchuk 

But After read my programme code, you understand the requirement. so please suggest me another way to perform this operation. when update records in same table.
[21 Mar 2012 17:54] b l vijaya kumar
Creating  Trigger in MySql:
mysql>     Delimiter //
mysql> Create trigger ins_trig before i
    -> For each row
    -> Begin
    -> Delete from emp where empno=2;
    -> End;
    -> //
Query OK, 0 rows affected (0.03 sec)
mysql> delimiter ;

While in the creation of trigger this syntax can create a trigger. But it won’t work will give u an error

mysql> insert into emp values(33,'cris','vizag',curtime());
ERROR 1442 (HY000): Can't update table 'emp' in stored function/trigger because
it is already used by statement which invoked this stored function/trigger.

Reason: it is we r using the same table twice for our purpose of sql statements to execute it gives the error cause when we r using more than 1 command on the same table in a trigger by default mysql creates a synchronization for the first statement so while executing second 1 on synchronized 1 gives u error .
[10 Oct 2019 22:05] MySQL Verification Team
https://bugs.mysql.com/bug.php?id=97189 marked as duplicate of this one.