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 Workaround needed for nested trigger problem ERROR 54038
Date Fri, 04 Jan 2008 06:34:40 GMT
Hi fellow Derby users. This is regarding the trigger bug (or is it) that
results in the message:
"ERROR 54038: Maximum depth of nested triggers was exceeded", from a
recursive trigger action.
http://issues.apache.org/jira/browse/DERBY-1261

I'm hoping someone has come up with a workaround that suites my situation. I
need to be
able to update a timestamp field in a record when the record is inserted or
updated. I don't want
to alter SQL inserts to include CURRENT TIMESTAMP because all the SQL is
generated as part of a
persistence framework solution and does not include CURRENT TIMESTAMP.

A trigger that updates a row AFTER INSERT works just fine when it is not
accompanied by an update trigger on the same table. A singular update
trigger, that itself updates the same table row, gets caught in the
recursion trap.

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.

CREATE TABLE ER.ENT_REL_ALLOW
(ENT_TYP_UID INTEGER NOT NULL,
REL_TYP_UID INTEGER NOT NULL,
ENT_FROM_TO CHAR(1) NOT NULL,
REC_TIMESTAMP TIMESTAMP NOT NULL,
CONSTRAINT ENT_REL_PK PRIMARY KEY (ENT_TYP_UID, REL_TYP_UID));

With no other triggers on this table, create update trigger that updates the
changed row:

CREATE TRIGGER ER.ENT_REL_ALLOW_TRIG2 AFTER UPDATE ON
ER.ENT_REL_ALLOWREFERENCING 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;

Insert into the table with no problem:

INSERT INTO ER.ENT_REL_ALLOW VALUES(11, 11, '1', '2008-01-03 20:00:00.000');
1 row inserted/updated/deleted

Update gets caught in recursive trigger trap:

ij> update ER.ENT_REL_ALLOW set ent_from_to = '0' where ent_typ_uid = 11 and
rel
_typ_uid = 11;
ERROR 54038: Maximum depth of nested triggers was exceeded.

Interestingly, this works as expected in DB2, as though the update action of
a trigger "doesn't count" as an update and does not itself invoke the update
trigger.

Thanks for any help or advice!

-Peder

Mime
View raw message