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
