Trigger for update of all columns of table
Posted in 1995
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,
pr_desc,pr_prod,pr_invnt,pr_uom,pr_oem,pr_lbs,pr_cubic,pr_ser,
pr_cursp,pr_date,pr_list,pr_pr1,pr_pr2,pr_pr3,pr_pr4,pr_pr5,
pr_pr6,pr_pr7,pr_pr8,pr_pr9,pr_chng,pr_puru,pr_stku,pr_salu,
pr_stand,pr_stmat,pr_stlab,pr_stoh,pr_sthrs,pr_sllev,pr_solev,
pr_shlev,pr_act,pr_acmat,pr_aclab,pr_acoh,pr_achrs,pr_allev,
pr_aolev,pr_ahlev,pr_lead,pr_assy,pr_mb,pr_fg,pr_comps,pr_nwu,
pr_last,pr_licst,pr_ficst,pr_wtcst,pr_pvend,pr_qtyb,pr_scost,
pr_fcost,pr_miord,pr_seas,pr_im,pr_abc,pr_cst1,pr_cst2,pr_cst3,
pr_mtrx,pr_ucat,pr_type,pr_stars on "afc".itemmain referencing
old as pre
for each row
(
update "afc".itemmain set "afc".itemmain.pr_lupdt = TODAY
where (pr_id = pre.pr_id ) );
Any suggestions?
Pete