[
http://issues.apache.org/jira/browse/DERBY-1261?page=comments#action_12418666 ]
Deepa Remesh commented on DERBY-1261:
-------------------------------------
In the context of DERBY-551 (enabling procedures in triggers), I have been
looking at behaviour of triggers and recursion. I came across this issue and on
looking at it, I tend to think the examples above are cases of recursion. Even
with just the first trigger, I think we should be getting "ERROR 54038: Maximum
depth of nested triggers was exceeded. ". I am basing this on the following
paragraph from section 4.38 of the spec:
"During the execution of an SQL-statement, zero or more trigger execution
contexts exist, no more
than one of which is active. The execution of an SQL-data change statement Si
creates at least one
new trigger execution context TECi and causes TECi to become active. TECi
remains in existence
until the completion of Si. An SQL-data change statement Sj that is executed
before the completion
of Si preserves TECi and creates a new trigger execution context TECj that
becomes the active one
and remains in existence until the completion of Sj. At the completion of Sj,
TECj ceases to exist
and TECi is restored as the active trigger execution context."
I think the example below with just one trigger should cause recursion when we
try to fire the trigger. But currently it does not and this looks like a bug to
me.
create table tab(i integer primary key, j integer);
insert into tab values (1, null);
insert into tab values (2, null);
create trigger t1
after update
on tab
referencing old as old for each row
mode db2sql
update tab set j=1 where i = old.i;
update tab set j=3;
When we execute the update statement ( update tab set j=3; ), it will create a
trigger execution context (TEC). The trigger firing will cause another update
statement (update tab set j=1 where i = old.i; --- this is the
triggered-sql-statement) to get executed. This new statement will in turn
create a new TEC. This will cause the same trigger to fire and that will create
another TEC and so on. Each TEC can execute the trigger once (if trigger event
is satisfied). As there will new TECs for each new update statement, the
trigger will get fired by each of the associated TECs. So I think this is a
case of recursion. I have not read the code surrounding this. This is just my
interpretation of the spec.
Thoughts/comments?
> 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
