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: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 5.0.41 | OS: | Linux |
Assigned to: | CPU Architecture: | Any |
[31 Aug 2009 8:56]
rajesh goswami
[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.