db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Peder Hultin" <peder.hul...@gmail.com>
Subject Re: Workaround needed for nested trigger problem ERROR 54038
Date Sat, 05 Jan 2008 00:11:13 GMT
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 <Oystein.Grovlen@sun.com> 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
>

Mime
View raw message