[
http://issues.apache.org/jira/browse/DERBY-1261?page=comments#action_12420298 ]
Fernanda Pizzorno commented on DERBY-1261:
------------------------------------------
I agree with Deepa's interpretation that a trigger should be able to fire
itself, but based on the following paragraph on section 4.38 of the spec, I
believe that the same row-level trigger should not be executed more than once
for the same row.
"If a row-level trigger RLT is considered as executed for some row R in SC,
then RLT is not subsequently executed for R."
I looked into this issue when I was working on other trigger related issues
(DERBY-1043 and DERBY-1064) and I found out that the behavior of the trigger
(recursive or not) changed when changes were made to the definition of the
table. Regardless of which interpretation of the spec is correct, the sudden
change on the behavior of the trigger is definitively a bug.
When I create the trigger "tr1" on table "t1" it is not recursive.
create table t1 (a int, b int);
insert into t1 (a) values (1), (2), (3), (4), (5);
create trigger tr1
after update on t1
referencing old as old
for each row mode db2sql
update t1 set b = 1 where a = old.a + 1;
update t1 set a = a + 10 where a = 1;
select * from t1;
A |B
-----------------------
11 |NULL
2 |1
3 |NULL
4 |NULL
5 |NULL
5 rows selected
If I later create a new trigger on table "t1" or add a column to table "t1" as
the two examples below show, the trigger "tr1" becomes recursive.
Example 1: creating a new trigger
create trigger tr2
after update on t1
referencing old as old
for each row mode db2sql
values(old.a);
update t1 set a = a + 10 where a = 1;
select * from t1;
A |B
-----------------------
11 |NULL
2 |1
3 |1
4 |1
5 |1
5 rows selected
Example 2: adding a column
alter table t1
add column c int;
update t1 set a = a + 10 where a = 1;
select * from t1;
A |B |C
-----------------------------------
11 |NULL |NULL
2 |1 |NULL
3 |1 |NULL
4 |1 |NULL
5 |1 |NULL
5 rows selected
It seems that changing the definition of "t1" (by creating a new trigger,
adding a new column, etc) caused the update statement inside the trigger "tr1"
to be invalidated and later recompiled. I suspect that the first time the
update statement is compiled table "t1" does not have a trigger "tr1" and
therefore the update statement does not fire triggers (UpdateResultSet with
deferred = false). When the update statement is recompiled (after changes in
the definition of the table), the table "t1" does have a trigger "tr1" and the
update statement fires triggers (deferred = true).
> Two triggers on same table cause "ERROR 54038: Maximum depth of nested
> triggers was exceeded."
> ----------------------------------------------------------------------------------------------
>
> Key: DERBY-1261
> URL: http://issues.apache.org/jira/browse/DERBY-1261
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded on Solaris x86
> Reporter: Øystein Grøvlen
>
> Two triggers on same table may lead to self-recursion:
> ij> create table t3(i integer primary key, j integer, t timestamp);
> 0 rows inserted/updated/deleted
> ij> create trigger tr3i after insert on t3 referencing new as new for each
> row mode db2sql update t3 set t = current_timestamp where i = new.i;
> 0 rows inserted/updated/deleted
> ij> insert into t3 values (1, 1, NULL);
> 1 row inserted/updated/deleted
> ij> create trigger tr3u after update on t3 referencing old as old for each
> row mode db2sql update t3 set t = current_timestamp where i = old.i;
> 0 rows inserted/updated/deleted
> ij> insert into t3 values (2, 1, NULL);
> ERROR 54038: Maximum depth of nested triggers was exceeded.
> ij> update t3 set j=j+1;
> 1 row inserted/updated/deleted
> ij> create trigger tr3u2 after update on t3 referencing old as old for each
> row mode db2sql update t3 set j = 0 where i = old.i and j > 2;
> 0 rows inserted/updated/deleted
> ij> update t3 set j=j+1;
> ERROR 54038: Maximum depth of nested triggers was exceeded.
> From derby.log:
> 2006-04-27 10:03:54.792 GMT Thread[main,5,main] (XID = 1274), (SESSIONID =
> 0), (DATABASE = testDB), (DRDAID = null), Cleanup action starting
> 2006-04-27 10:03:54.792 GMT Thread[main,5,main] (XID = 1274), (SESSIONID =
> 0), (DATABASE = testDB), (DRDAID = null), Failed Statement is: insert into t3
> values (2, 1, NULL)
> ERROR 54038: Maximum depth of nested triggers was exceeded.
> at
> org.apache.derby.iapi.error.StandardException.newException(StandardException.java:301)
> at
> org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.pushTriggerExecutionContext(GenericLanguageConnectionContext.java:2104)
> at
> org.apache.derby.impl.sql.execute.InternalTriggerExecutionContext.<init>(InternalTriggerExecutionContext.java:179)
> at
> org.apache.derby.impl.sql.execute.GenericExecutionFactory.getTriggerExecutionContext(GenericExecutionFactory.java:302)
> at
> org.apache.derby.impl.sql.execute.TriggerEventActivator.<init>(TriggerEventActivator.java:105)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.fireBeforeTriggers(UpdateResultSet.java:798)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:283)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:361)
> at
> org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(GenericTriggerExecutor.java:169)
> at
> org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(RowTriggerExecutor.java:110)
> at
> org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(TriggerEventActivator.java:277)
> at
> org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(InsertResultSet.java:1134)
> at
> org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:522)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:361)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1161)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:567)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:497)
> at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:313)
> at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:433)
> at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:310)
> at org.apache.derby.impl.tools.ij.Main.go(Main.java:203)
> at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:169)
> at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55)
> at org.apache.derby.tools.ij.main(ij.java:60)
> Cleanup action completed
> 2006-04-27 10:06:18.589 GMT Thread[main,5,main] (XID = 1293), (SESSIONID =
> 0), (DATABASE = testDB), (DRDAID = null), Cleanup action starting
> 2006-04-27 10:06:18.589 GMT Thread[main,5,main] (XID = 1293), (SESSIONID =
> 0), (DATABASE = testDB), (DRDAID = null), Failed Statement is: update t3 set
> j=j+1
> ERROR 54038: Maximum depth of nested triggers was exceeded.
> at
> org.apache.derby.iapi.error.StandardException.newException(StandardException.java:301)
> at
> org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.pushTriggerExecutionContext(GenericLanguageConnectionContext.java:2104)
> at
> org.apache.derby.impl.sql.execute.InternalTriggerExecutionContext.<init>(InternalTriggerExecutionContext.java:179)
> at
> org.apache.derby.impl.sql.execute.GenericExecutionFactory.getTriggerExecutionContext(GenericExecutionFactory.java:302)
> at
> org.apache.derby.impl.sql.execute.TriggerEventActivator.<init>(TriggerEventActivator.java:105)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.fireBeforeTriggers(UpdateResultSet.java:798)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:283)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:361)
> at
> org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(GenericTriggerExecutor.java:169)
> at
> org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(RowTriggerExecutor.java:110)
> at
> org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(TriggerEventActivator.java:277)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTriggers(UpdateResultSet.java:825)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:288)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:361)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1161)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:567)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:497)
> at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:313)
> at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:433)
> at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:310)
> at org.apache.derby.impl.tools.ij.Main.go(Main.java:203)
> at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:169)
> at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55)
> at org.apache.derby.tools.ij.main(ij.java:60)
> Cleanup action completed
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
