db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Fernanda Pizzorno (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 Tue, 11 Jul 2006 12:31:32 GMT
    [ 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


Mime
View raw message