Re: Trigger for update of all columns of table

> I have a field (in_lupdt) in a table(itemmain) that I've recently > added to hold the date that the row was last updated. I made the > following trigger to do this so that I wouldn't have to change all > the programs that update this table. Unfortunatly, many of the > programs do the updates like this: > > update itemmain set * = pr_itemmain.* where ... ...> > Now I get errors saying: > > -747 Table or column matches object referenced in triggering > statement. > > create trigger "pete".itemmain_update update of pr_cmpy,pr_id, ... > for each row > ( > update "afc".itemmain set "afc".itemmain.pr_lupdt = TODAY > where (pr_id = pre.pr_id ) ); > > Any suggestions? > > Pete Trigger language simply won't allow you to make any changes to the table that caused the trigger to fire, even from within a stored procedure that gets called. This is their way of protecting you from putting triggers into infinite loops. Yes it is a pain that this prevents you from maintaining "date last updated" columns. The only thing I haven't tried is narrowing down the column list in the "update of" clause - I suppose there's just a slim chance the 'protection' might be implemented at column rather than table level on an update trigger. akent@cix.compulink.co.uk (Andy Kent) ------------------------------------------------ Freelance Informix Database Specialist, Redland, Bristol, England