Index: java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java =================================================================== --- java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java (revision 429485) +++ java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java (working copy) @@ -257,22 +257,16 @@ */ UUID tmpTriggerId = dd.getUUIDFactory().createUUID(); - /* - ** If we have a WHEN action we create it now. - */ + actionSPSId = (actionSPSId == null) ? + dd.getUUIDFactory().createUUID() : actionSPSId; + DataDescriptorGenerator ddg = dd.getDataDescriptorGenerator(); - if (whenText != null) - { - whenspsd = createSPS(lcc, ddg, dd, tc, tmpTriggerId, triggerSd, - whenSPSId, spsCompSchemaId, whenText, true, triggerTable); - } - + /* - ** Create the trigger action - */ - actionspsd = createSPS(lcc, ddg, dd, tc, tmpTriggerId, triggerSd, - actionSPSId, spsCompSchemaId, actionText, false, triggerTable); - + ** Create the trigger descriptor first so the trigger action + ** compilation can pick up the relevant trigger especially in + ** the case of self triggering. + */ TriggerDescriptor triggerd = ddg.newTriggerDescriptor( triggerSd, @@ -284,7 +278,7 @@ isEnabled, triggerTable, whenspsd == null ? null : whenspsd.getUUID(), - actionspsd.getUUID(), + actionSPSId, creationTimestamp == null ? new Timestamp(System.currentTimeMillis()) : creationTimestamp, referencedCols, originalActionText, @@ -297,8 +291,23 @@ dd.addDescriptor(triggerd, triggerSd, DataDictionary.SYSTRIGGERS_CATALOG_NUM, false, tc); + /* + ** If we have a WHEN action we create it now. + */ + if (whenText != null) + { + whenspsd = createSPS(lcc, ddg, dd, tc, tmpTriggerId, triggerSd, + whenSPSId, spsCompSchemaId, whenText, true, triggerTable); + } /* + ** Create the trigger action + */ + actionspsd = createSPS(lcc, ddg, dd, tc, tmpTriggerId, triggerSd, + actionSPSId, spsCompSchemaId, actionText, false, triggerTable); + + + /* ** Make underlying spses dependent on the trigger. */ if (whenspsd != null) Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java (revision 429485) +++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java (working copy) @@ -1210,7 +1210,7 @@ rs.next(); System.out.println("column 1 on this row is " + rs.getInt(1)); System.out.println("this update row will fire the update trigger which will update all the rows in the table to have c1=1 and hence no more rows will qualify for the resultset"); - rs.updateLong(1,123); + rs.updateLong(2,2); rs.updateRow(); rs.next(); try { @@ -2456,9 +2456,9 @@ stmt.executeUpdate("create trigger tr1 after delete on table0WithTriggers for each statement mode db2sql insert into deleteTriggerInsertIntoThisTable values (1)"); stmt.executeUpdate("create trigger tr2 after update on table0WithTriggers for each statement mode db2sql insert into updateTriggerInsertIntoThisTable values (1)"); stmt.executeUpdate("create table table1WithTriggers (c1 int, c2 bigint)"); - stmt.executeUpdate("create trigger tr3 after delete on table1WithTriggers for each statement mode db2sql delete from table1WithTriggers"); + stmt.executeUpdate("create trigger tr3 after delete on table1WithTriggers referencing old as old for each row mode db2sql delete from table1WithTriggers where c1=old.c1+1 or c1=old.c1-1"); stmt.executeUpdate("create table table2WithTriggers (c1 int, c2 bigint)"); - stmt.executeUpdate("create trigger tr4 after update on table2WithTriggers for each statement mode db2sql update table2WithTriggers set c1=1"); + stmt.executeUpdate("create trigger tr4 after update of c2 on table2WithTriggers for each statement mode db2sql update table2WithTriggers set c1=1"); stmt.executeUpdate("create table selfReferencingT1 (c1 char(2) not null, c2 char(2), constraint selfReferencingT1 primary key(c1), constraint manages1 foreign key(c2) references selfReferencingT1(c1) on delete cascade)"); stmt.executeUpdate("create table selfReferencingT2 (c1 char(2) not null, c2 char(2), constraint selfReferencingT2 primary key(c1), constraint manages2 foreign key(c2) references selfReferencingT2(c1) on update restrict)"); Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql (revision 429485) +++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql (working copy) @@ -602,3 +602,13 @@ create trigger mytrigger after update on myschema.mytable for each row mode db2sql select * from sys.systables; rollback; +create table test (testid integer not null + generated always as identity (start with 1, increment by 1), + info integer not null, ts timestamp not null default '1980-01-01-00.00.00.000000'); +create trigger update_test + after update on test + referencing old as old + for each row mode db2sql + update test set ts=current_timestamp where testid=old.testid; +insert into test(info) values (1),(2),(3); +UPDATE TEST SET INFO = 1 WHERE TESTID = 2; Index: java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out (revision 429485) +++ java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out (working copy) @@ -1196,12 +1196,10 @@ create trigger tgood after insert on x for each statement mode db2sql insert into x values 666; 0 rows inserted/updated/deleted ij> insert into x values 1; -1 row inserted/updated/deleted +ERROR 54038: Maximum depth of nested triggers was exceeded. ij> select * from x; X ----------- -1 -666 ij> drop trigger tgood; 0 rows inserted/updated/deleted ij> create trigger tgood after insert on x for each statement mode db2sql delete from x; @@ -1251,4 +1249,18 @@ ij(CONNECTION1)> create trigger mytrigger after update on myschema.mytable for each row mode db2sql select * from sys.systables; 0 rows inserted/updated/deleted ij(CONNECTION1)> rollback; +ij(CONNECTION1)> create table test (testid integer not null + generated always as identity (start with 1, increment by 1), + info integer not null, ts timestamp not null default '1980-01-01-00.00.00.000000'); +0 rows inserted/updated/deleted +ij(CONNECTION1)> create trigger update_test + after update on test + referencing old as old + for each row mode db2sql + update test set ts=current_timestamp where testid=old.testid; +0 rows inserted/updated/deleted +ij(CONNECTION1)> insert into test(info) values (1),(2),(3); +3 rows inserted/updated/deleted +ij(CONNECTION1)> UPDATE TEST SET INFO = 1 WHERE TESTID = 2; +ERROR 54038: Maximum depth of nested triggers was exceeded. ij(CONNECTION1)>