trigger problem

Hi, I want to have a trigger on a table, that delete the oldest row when a new one is inserted. Here is my trigger : CREATE TRIGGER TR_T_PURGE_ERRORINSERT ON TB_ERROR BEFORE(EXECUTE PROCEDURE tb_theProc()); My procedure : CREATE PROCEDURE TB_theProc()DEFINE nb_max_errors INTEGER; DEFINE nb_res INTEGER; DEFINE id_to_del INTEGER; LET nb_max_errors=10000; LET nb_res=(select count(*) from tb_error); IF nb_res > nb_max_errors THEN WHILE nb_res-nb_max_errors > 0 select min(error_id) into id_to_del from tb_error for read only; delete from tb_error_details where serial_error=id_to_del; delete from tb_error where error_id=id_to_del; LET nb_res=nb_res-1; END WHILE END IF END PROCEDURE; The problem is i get this error on the procedure : Err -747 : Table or column matches object referenced in triggering statement. I have a index on tb_error column error_id, and the table is in mode lock row. Did i missed something or is this impossible to do ? Thank you all Yd

> -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of YANN > DELANOE > Sent: Monday, September 08, 2008 3:00 AM > To: ids@iiug.org > Subject: trigger problem [13300] > > Hi, > > I want to have a trigger on a table, that delete the oldest row when a new > one > is inserted. The rule used to be that your trigger couldn't insert/update/delete the table that the trigger was on. I think that still applies, but I can't find it in the manual. A stored procedure that runs periodically and cleans up your old records would solve your problem, I think. --EEM > > Here is my trigger : > CREATE TRIGGER TR_T_PURGE_ERROR> INSERT ON TB_ERROR > BEFORE(EXECUTE PROCEDURE tb_theProc()); > > My procedure : > CREATE PROCEDURE TB_theProc()> DEFINE nb_max_errors INTEGER; > DEFINE nb_res INTEGER; > DEFINE id_to_del INTEGER; > > LET nb_max_errors=10000; > LET nb_res=(select count(*) from tb_error); > IF nb_res > nb_max_errors THEN > > WHILE nb_res-nb_max_errors > 0 > > select min(error_id) into id_to_del from tb_error for read only; > > delete from tb_error_details where serial_error=id_to_del;> > delete from tb_error where error_id=id_to_del;> > LET nb_res=nb_res-1; > > END WHILE > END IF > END PROCEDURE; > > The problem is i get this error on the procedure : > Err -747 : Table or column matches object referenced in triggering > statement. > > I have a index on tb_error column error_id, and the table is in mode lock > row. > > Did i missed something or is this impossible to do ? > > Thank you all > Yd > > > ************************************************************************ ** > ***** > Forum Note: Use "Reply" to post a response in the discussion forum.