Index: java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java =================================================================== --- java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java (revision 429817) +++ java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java (working copy) @@ -256,23 +256,17 @@ */ 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 + ** Create the trigger descriptor first so the trigger action + ** compilation can pick up the relevant trigger especially in + ** the case of self triggering. */ - actionspsd = createSPS(lcc, ddg, dd, tc, tmpTriggerId, triggerSd, - actionSPSId, spsCompSchemaId, actionText, false, triggerTable); - - TriggerDescriptor triggerd = + TriggerDescriptor triggerd = ddg.newTriggerDescriptor( triggerSd, tmpTriggerId, @@ -283,7 +277,7 @@ isEnabled, triggerTable, whenspsd == null ? null : whenspsd.getUUID(), - actionspsd.getUUID(), + actionSPSId, creationTimestamp == null ? new Timestamp(System.currentTimeMillis()) : creationTimestamp, referencedCols, originalActionText, @@ -297,7 +291,23 @@ 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 429817) +++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java (working copy) @@ -1290,7 +1290,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 { @@ -3108,9 +3108,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 429817) +++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql (working copy) @@ -625,3 +625,15 @@ SELECT * FROM T10642; SELECT * FROM T10641_DELETIONS; SELECT * FROM T10642_DELETIONS; + +-- DERBY-1652 +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 429817) +++ java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out (working copy) @@ -842,12 +842,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; @@ -875,46 +873,36 @@ for each statement mode db2sql insert into x values (666), (999), (333); 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 order by 1; X ----------- -1 -333 -666 -999 ij> drop trigger tgood; 0 rows inserted/updated/deleted ij> delete from x; -4 rows inserted/updated/deleted +0 rows inserted/updated/deleted ij> create trigger tgood after insert on x referencing new as n for each row mode db2sql insert into x values (n.x); 0 rows inserted/updated/deleted ij> insert into x values 7; -1 row inserted/updated/deleted +ERROR 54038: Maximum depth of nested triggers was exceeded. ij> select * from x order by 1; X ----------- -7 -7 ij> drop trigger tgood; 0 rows inserted/updated/deleted ij> delete from x; -2 rows inserted/updated/deleted +0 rows inserted/updated/deleted ij> create trigger tgood after insert on x referencing new as n for each row mode db2sql insert into x values (333), (999), (333); 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 order by 1; X ----------- -1 -333 -333 -999 ij> drop trigger tgood; 0 rows inserted/updated/deleted ij> -- DERBY-1204 trigger causes StringIndexOutOfBoundsException @@ -1133,4 +1121,19 @@ ----------- 0 1 +ij> -- DERBY-1652 +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> 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> insert into test(info) values (1),(2),(3); +3 rows inserted/updated/deleted +ij> UPDATE TEST SET INFO = 1 WHERE TESTID = 2; +ERROR 54038: Maximum depth of nested triggers was exceeded. ij>