Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <29525627.1155670454847.JavaMail.jira@brutus>
Date: Tue, 15 Aug 2006 12:34:14 -0700 (PDT)
From: "Kathey Marsden (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@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
In-Reply-To: <7488728.1154959911426.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

    [ http://issues.apache.org/jira/browse/DERBY-1652?page=comments#action_12428199 ] 
            
Kathey Marsden commented on DERBY-1652:
---------------------------------------

Yip said
>I thought they were "negative" tests with wrong results
Thanks Yip,  I think I got confused because of the comments: "--after" and the trigger name: "tgood"  don't lend themselves to a negative test.  If more work is done on the patch it might be good to change them for clarity moving forward.

ij> -- after
create trigger tgood after insert on x for each statement mode db2sql insert into x values 666;
0 rows inserted/updated/deleted
ij> insert into x values 1;
ERROR 54038: Maximum depth of nested triggers was exceeded.

> 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.0.0, 10.1.3.1
>            Reporter: Kathey Marsden
>         Assigned To: Yip Ng
>             Fix For: 10.2.0.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