CODEWITHSUNDEEP
mysqlsqlmysql-error-1442
user591931
user591931

Reputation: 249

mysql 5.2.3 syntax error on procedure invoked inside a trigger

DELIMITER |

CREATE TRIGGER RV1       
after insert on Iscr 
for each row 
call ProcRV1(new.Usere,new.Sub);

create procedure ProcRV1(nUsere varchar(15),nSub varchar(15))

delete from Iscr

where Usere=nUsere && Sub=nSub && Usere=Sub

|

with this on table Iscr i get ERROR 1442 when i try to insert rows any help ??

thanks

Upvotes: 0

Views: 344

Answers (1)

Nishant
Nishant

Reputation: 55866

    DELIMITER |

    create procedure ProcRV1(nUsere varchar(15),nSub varchar(15))
    BEGIN
        delete from Iscr where Usere=nUsere && Sub=nSub && Usere=Sub;
    END
    |

    CREATE TRIGGER RV1       
        after insert on Iscr 
        for each row 
        BEGIN
            call ProcRV1(new.Usere,new.Sub);
        END
    |

    DELIMITER ;

Edit: on seeing your SPs now:

OK, So you are trying to delete rows from the current table while inserting in it. MySQL does not allow that. But there must be a trick. Something like done here http://crazytoon.com/2008/03/03/mysql-error-1442-hy000-cant-update-table-t1-in-stored-functiontrigger-because-it-is-already-used-by-statement-which-invoked-this-stored-functiontrigger/

Refer this: 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.

Upvotes: 1

Related Questions