db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Deepa Remesh (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1261) Two triggers on same table cause "ERROR 54038: Maximum depth of nested triggers was exceeded."
Date Fri, 30 Jun 2006 15:21:30 GMT
    [ 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


Mime
View raw message