ERROR 1442 (HY000) when delete inside trigger statement

i have two table, T1, T2. and 1 trigger.

trigger is before update on T1 and it updates some values in T2. once
it's done, the trigger tries to delete the subject row of T1 (delete
from T1 where id = new.id)

i tried with second trigger on T2 (after/before update) and with a
procedure inside this trigger.. but, all the time i get..

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

found http://forums.mysql.com/read.php?99,122354,122354#msg-122354 and
many other articles which had ended up with no solution. (with before
update, it's possible to set new values to NEW.*, but did not find
anything useful to do a successful delete)


could somebody please confirm this is not possible!!! so i can think
about some other workaround :)

thanks

~viraj

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: ERROR 1442 (HY000) when delete inside trigger statement

------- Original Message -------
> From: viraj
> To: mysql@lists.mysql.com
> Sent: 19/2/10, 05:48:41
> Subject: ERROR 1442 (HY000) when delete > inside trigger statement

> issue: ERROR 1442 (HY000): Can't update table 'T1' in stored
> function/trigger because it is already used by > statement which invoked
> this stored function/trigger.

> could somebody please confirm this is not possible!!! so i can think
> about some other workaround :)

--

That is correct. There is as far as I know no way in a MySQL trigger to neither to do operations on the table the trigger belongs to (obviously except the row that the trigger is operating on through the NEW variables) nor reject an insert, update, or delete.

It is of course possible to do a workaround in a stored procedure and use permissions to ensure that the normal users cannot update the table directly. I don't know whether that will be an acceptable solution in your case though.

Jesper

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: ERROR 1442 (HY000) when delete inside trigger statement

>
> That is correct. There is as far as I know no way in a MySQL trigger to n=
either to do operations on the table the trigger belongs to (obviously exce=
pt the row that the trigger is operating on through the NEW variables) nor =
reject an insert, update, or delete.
>

thanks jesper.

~viraj

> It is of course possible to do a workaround in a stored procedure and use=
permissions to ensure that the normal users cannot update the table direct=
ly. I don't know whether that will be an acceptable solution in your case t=
hough.
>
> Jesper
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=3Dkalinga=
@gmail.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg