Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 94261 invoked from network); 7 Aug 2006 17:21:34 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 7 Aug 2006 17:21:34 -0000 Received: (qmail 13232 invoked by uid 500); 7 Aug 2006 17:21:34 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 13193 invoked by uid 500); 7 Aug 2006 17:21:33 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 13184 invoked by uid 99); 7 Aug 2006 17:21:33 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 07 Aug 2006 10:21:33 -0700 X-ASF-Spam-Status: No, hits=1.3 required=10.0 tests=DNS_FROM_RFC_ABUSE,HTML_MESSAGE,INFO_TLD,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: domain of yipng168@gmail.com designates 64.233.182.185 as permitted sender) Received: from [64.233.182.185] (HELO nf-out-0910.google.com) (64.233.182.185) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 07 Aug 2006 10:21:33 -0700 Received: by nf-out-0910.google.com with SMTP id b2so2218417nfe for ; Mon, 07 Aug 2006 10:21:11 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=f+YXLafA7eHMiE9j6gOfwgDYLEIKAwA8dm0vC6lrVFs8vTYBoYD1B7VdpoHTnvyvqK1+yzWYpwGG94XIPav4jp2tANA6zotwREifwCKsCgAhFk6eNYfA4udhXWl10wC7Z++6czgl++dHpQ46+SI7JyQsUQ/YYNFuCTDzzNrrq5Q= Received: by 10.78.147.3 with SMTP id u3mr2449596hud; Mon, 07 Aug 2006 10:20:39 -0700 (PDT) Received: by 10.78.139.9 with HTTP; Mon, 7 Aug 2006 10:20:39 -0700 (PDT) Message-ID: Date: Mon, 7 Aug 2006 10:20:39 -0700 From: "Yip Ng" To: derby-dev@db.apache.org Subject: Re: [jira] Commented: (DERBY-1603) ERROR 54038: "Maximum depth of nested triggers was exceeded" occurs when trigger fires after upating 10.1.2.5 jars to 10.1.3.1 In-Reply-To: <44D76B16.7060101@sbcglobal.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_108753_2800356.1154971239292" References: <1010248.1154926760338.JavaMail.jira@brutus> <44D7416A.4020209@sbcglobal.net> <44D753BF.3010902@sbcglobal.net> <44D75833.5070308@sbcglobal.net> <44D75B4F.9030409@apache.org> <44D76B16.7060101@sbcglobal.net> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N ------=_Part_108753_2800356.1154971239292 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Another way is to specify which column in the trigger that will invoke the trigger action. 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; On 8/7/06, Kathey Marsden wrote: > > Daniel John Debrunner wrote: > > >Then I think this would lead to a simpler action statement of > > > >UPDATE TEST SET TIMESTAMP = CURRENT_TIMESTAMP WHERE TESTID = OLD.TESTID > > > >A statement trigger might be best in this case, would result in a single > >update statement rather than N. > > > > > > > Hmmm.... > Well, this (I think) is where we started and from what I understand > from Yip is the case that is always supposed to give us the "Maximum > depth of nested triggers was exceeded error" (DERBY-1652). If the > trigger is defined this way, when the row is updated, it fires the > update trigger which updates the row which fires the update > trigger etc... > > I added the additional qualifier ( > > > AND INFO != OLD.INFO > > to make it stop on the second fire. Is there a better way? > > Kathey > > > ------=_Part_108753_2800356.1154971239292 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Another way is to specify which column in the trigger that will invoke the trigger action. 
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;


On 8/7/06, Kathey Marsden < kmarsdenderby@sbcglobal.net> wrote:
Daniel John Debrunner wrote:

>Then I think this would lead to a simpler action statement of
>
>UPDATE TEST SET TIMESTAMP = CURRENT_TIMESTAMP WHERE TESTID = OLD.TESTID
>
>A statement trigger might be best in this case, would result in a single
>update statement rather than N.
>
>
>
Hmmm....
Well, this (I think)  is where we started and from what I understand
from Yip is  the case that is always supposed to give us the "Maximum
depth of nested triggers was exceeded error" (DERBY-1652).  If the
trigger is defined this way, when the row is updated, it fires the
update trigger which updates the row which fires the update trigger  etc...

I added the additional qualifier (

> AND INFO != OLD.INFO

to make it stop on the second fire. Is there a better way?

Kathey



------=_Part_108753_2800356.1154971239292--