FANTASTIC!!! This idea works perfectly; thanks so much! This has delayed me quite a number of hours. I did the following:
CREATE TRIGGER ER.ENT_REL_ALLOW_TRIG2 AFTER UPDATE of ENT_TYP_UID, REL_TYP_UID, ENT_FROM_TO ON ER.ENT_REL_ALLOW REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL UPDATE ER.ENT_REL_ALLOW set REC_TIMESTAMP = CURRENT TIMESTAMP where ENT_TYP_UID = NEW_ROW.ENT_TYP_UID and REL_TYP_UID = NEW_ROW.REL_TYP_UID; This allowed update of the row without error msgs and also updated the timestamp column. It even works to include REC_TIMESTAMP in the update statement, pulling in the CURRENT TIMESTAMP: update ER.ENT_REL_ALLOW set ent_from_to = '0', rec_timestamp = '2007-01-01 20:00:00.123' where ent_typ_uid = 11 and rel_typ_uid = 11; ENT_TYP_UID|REL_TYP_UID|&|REC_TIMESTAMP -------------------------------------------------- 11 |11 |0|2008-01-04 15:56:18.203 On Jan 3, 2008 11:48 PM, Øystein Grøvlen <[EMAIL PROTECTED]> wrote: > Peder Hultin wrote: > > Can someone recommend a way to update a timestamp field on insert and > > update and not get caught in this trap, without referencing CURRENT > > TIMESTAMP in insert or update SQL? I suspect this might not be possible. > > Any creative thinking much appreciated. Below is the DDL and SQL I'm > > using. > > I think a work-around is to limit the trigger to only fire on updates to > all columns except the timestamp column. To do this you have to list > all columns for which the update should cause the trigger to fire: > > CREATE TRIGGER trig AFTER UPDATE OF column1, column2, ... ON tab ... > > I have not tried this myself, but hopefully it should prevent the > recursion. > > -- > Øystein >
