db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yip Ng (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1652) Update trigger updating the same rows as the original update does not throw an exception ERROR 54038: "Maximum depth of nested triggers was exceeded" as it should
Date Mon, 28 Aug 2006 00:30:23 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1652?page=comments#action_12430906 ] 
            
Yip Ng commented on DERBY-1652:
-------------------------------

Here is the release note for this jira:


Release Note for DERBY-1652
-------------------------------------------

PROBLEM

   In some cases, an after update trigger does not get fired upon itself when its trigger
action contains 
an update statement on the trigger's subject table.


SYMPTOMS

   (1)  When defining a trigger for the first time for a table, e.g.:

  
        CREATE TABLE "TEST" ("TESTID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START
WITH 1, INCREMENT BY 1),
                             "INFO" INTEGER NOT NULL,
                             "TIMESTAMP" 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 TIMESTAMP = CURRENT_TIMESTAMP WHERE TESTID = OLD.TESTID;
  
        INSERT INTO TEST (INFO) VALUES (1), (2), (3);

        UPDATE TEST SET INFO = 1 WHERE TESTID = 2;   


        The above update statement executes successfully which it is incorrect.  The system
should have issued 
        SQLSTATE 54038 since it self-triggers to its maximum depth of 16.

   
   (2)  With the above example, when an user upgrades to a higher version and issues the same
update statement:

        UPDATE TEST SET INFO = 1 WHERE TESTID = 2;  
        ERROR 54038: Maximum depth of nested triggers was exceeded. 

	The SQLSTATE 54038 is issued in this case because after database upgrade, the trigger action
will be 
        invalidated by the system and will force a recompilation of the trigger when it is
fired.  The system
        generates the correct execution plan this time and since the trigger behavior have
changed, this might 
        cause applications to break unexpectedly.


CAUSE

   Derby's did not generate the correct execution plan for self-trigger invocation when such
a trigger is declared
   for the first time on the subject table; hence, resulting in the stated problem above.
 The affected version is 
   Derby 10.0 and 10.1.


SOLUTION

   A fix to resolve the above Derby symptom is available in 10.1 and 10.2.


WORKAROUND

   If self-trigger invocation was not intended by the application, the application can select
which column(s) on the
   update statement can cause the trigger to fire in the cREATE TRIGGER statement.  i.e.:

   CREATE TRIGGER update_test
   AFTER UPDATE OF INFO ON test
   REFERENCING OLD AS old
   FOR EACH ROW MODE DB2SQL
       UPDATE test SET timestamp=current_timestamp WHERE testid=old.testid;
 
   In the above statement, the trigger will only fire when an update is made to the "info"
column instead of any column(s).

   

> Update trigger updating the same rows as the original update does not  throw an exception
ERROR 54038: "Maximum depth of nested triggers was exceeded" as it should
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-1652
>                 URL: http://issues.apache.org/jira/browse/DERBY-1652
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.0.2.0, 10.2.1.0, 10.1.3.1
>            Reporter: Kathey Marsden
>         Assigned To: Yip Ng
>             Fix For: 10.2.1.0, 10.1.3.2
>
>         Attachments: derby1652-10.1.3-diff.txt, derby1652-10.1.3-stat.txt, derby1652-trunk-diff01.txt,
derby1652-trunk-stat01.txt
>
>
> Execution  of  an update trigger that updates the same row  as the original update will
 recurse forever and exceed the maximum nesting level of 16 so should throw the exception:
> ERROR 54038: "Maximum depth of nested triggers was exceeded"
> However, it  does not always throw the exception.   For example:
> CREATE TABLE "TEST" (                                           
>       
>  "TESTID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START 
>  WITH 1,
>  INCREMENT BY 1),                                                
>       
>  "INFO" INTEGER NOT NULL,                                        
>       
>  "TIMESTAMP" 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 TIMESTAMP = CURRENT_TIMESTAMP WHERE  
>   TESTID = OLD.TESTID;                                 
>  INSERT INTO TEST (INFO) VALUES  
>  (1),                            
>  (2),                            
>  (3); 
>  UPDATE TEST SET INFO = 1 WHERE TESTID = 2; 
> Does not throw an exception:
> However, If the derby jars are updated to a new version, the correct exception is thrown.
>  Replace derby jars with  new version
>  Execute the following in ij:
>  UPDATE TEST SET INFO = 1 WHERE TESTID = 2; 
>  ERROR 54038: Maximum depth of nested triggers was exceeded.
> Note: This issue stemmed from the Invalid issue,  DERBY-1603, because a user hit the
exception after upgrade and thought the exception after upgrade, not the lack of exception
before upgrade was the problem. This may be a common user error, so  we need a release note
to help mitigate the issue.    I will add one shortly after confirming the correct trigger
syntax. 

-- 
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