Can't update table 'tbl' in stored function/trigger because it is already used by statement

You'll probably get this error if you try to execute an Insert/Update/Delete statement inside a trigger. 

The following will fail !!

create trigger DB.Trg_Upd_Sample

before update ON SDB.Test 

FOR EACH ROW

BEGIN

If NEW.rowstatus = 0 Then

Update Test set new.rowstatus =1 where TestId = new.TestId;

End If ;

End ;

You need to use "Set" to update the actual columns.

Insert/Update/Delete statement can be used inside a trigger if they targer another table.

create trigger DB.Trg_Upd_Sample

before update ON SDB.Test 

FOR EACH ROW

BEGIN

If NEW.rowstatus = 0 Then

Set New.rowstatus = 2 ;

End If ;

End ;

(from Mysql 5 Ref Manual)

The OLD and NEW keywords enable you to access columns in the rows affected by a trigger. (OLD and NEW are not case sensitive.) In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated. 

Franck Leveneur

GreenLit Design

http://www.greenlitdesign.com