Bug #17549 can't manipulate table from within it's own trigger
Submitted: 19 Feb 2006 0:57 Modified: 19 Feb 2006 10:47
Reporter: Roland Volkmann Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.18-nt OS:Microsoft Windows (WinXP Prof.)
Assigned to: CPU Architecture:Any

[19 Feb 2006 0:57] Roland Volkmann
Description:
My table should contain only a maximum number of rows. Therefore an after insert trigger should delete the wasted old rows. But this results in error "ERROR 1442 (HY000): Can't update table 'ttt' in stored function/trigger because
it is already used by statement which invoked this stored function/trigger."

Because such limitation isn't described in the manual, I guess it's a bug. I would understand the behaviour, if the current new row would be affected, but it isn't (see example below).

Roland Volkmann
Stuttgart (Germany)

How to repeat:
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table ttt (id integer, primary key (id));
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter //
mysql> create trigger tr_ttt_ai after insert on ttt
    -> for each row
    -> begin
    -> declare $rows integer;
    -> select count(*) into $rows from ttt;
    -> if $rows > 2 then
    ->   delete from ttt order by id limit 1;
    -> end if;
    -> end //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> insert into ttt (id) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ttt (id) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ttt (id) values (3);
ERROR 1442 (HY000): Can't update table 'ttt' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
mysql>
[19 Feb 2006 10:47] Valeriy Kravchuk
Thank you for a problem report. Sorry, but changes to the table that FOR EACH ROW trigger is created on are not allowed (and, I hope, will never be supported). Period. Partially, the reason can be found in the manual:

http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html:

"In general, you cannot modify a table and select from the same table in a subquery."

But it is obviously should not be allowed.