Re: Trigger for update of all columns of table

Andy Kent (akent@cix.compulink.co.uk) wrote: : > 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 I think that you can update a table that caused a trigger. At least my one and only attempt (just starting to see how these things worked) updated one column in a table each time a different column was changed. I suspect that you may have to list each column in the table that should force the trigger (and not include the one to be updated by the trigger 'cause this would cause an endless loop). Ray Spinhirne Director Computer Services St. Edwards University