Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 2187 invoked from network); 12 Aug 2005 06:58:38 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 12 Aug 2005 06:58:38 -0000 Received: (qmail 88393 invoked by uid 500); 12 Aug 2005 06:58:38 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 88167 invoked by uid 500); 12 Aug 2005 06:58:37 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 88154 invoked by uid 99); 12 Aug 2005 06:58:36 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 11 Aug 2005 23:58:36 -0700 X-ASF-Spam-Status: No, hits=0.7 required=10.0 tests=DNS_FROM_RFC_ABUSE,FORGED_RCVD_HELO,HTML_10_20,HTML_MESSAGE X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [66.163.170.8] (HELO smtp110.mail.sc5.yahoo.com) (66.163.170.8) by apache.org (qpsmtpd/0.29) with SMTP; Thu, 11 Aug 2005 23:58:58 -0700 Received: (qmail 41829 invoked from network); 12 Aug 2005 06:58:35 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Received:Message-Id:X-Sender:X-Mailer:Date:To:From:Subject:In-Reply-To:References:Mime-Version:Content-Type; b=rvAJaQSqBZVKKzgN/hYjUCJm5ziUv09VGhbNYYdMK7tnc5f2VYFP+RXTl+2st5wqE0frcTd0dUp6KVw2R+kZWPvutmp6pUXTrJXhfT11C7+SSEoC/xMyU6591Zjpi1bqCIbKI4z2daNWgAAMtTPk8eq66IBHOb3eMCGeBqUNzUc= ; Received: from unknown (HELO sadlpt.yahoo.com) (demir4@67.123.87.114 with login) by smtp110.mail.sc5.yahoo.com with SMTP; 12 Aug 2005 06:58:35 -0000 Message-Id: <5.2.0.9.0.20050811233950.03037ae0@pop.mail.yahoo.com> X-Sender: demir4@pop.mail.yahoo.com X-Mailer: QUALCOMM Windows Eudora Version 5.2.0.9 Date: Thu, 11 Aug 2005 23:58:33 -0700 To: "Derby Discussion" From: Ali Demir Subject: Re: Use of If control statement in row level trigger In-Reply-To: <74130ca10508112327708541aa@mail.gmail.com> References: <5.2.0.9.0.20050811220740.03ca3118@pop.mail.yahoo.com> <74130ca1050811220363ad89b0@mail.gmail.com> <5.2.0.9.0.20050811220740.03ca3118@pop.mail.yahoo.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=====================_60253550==.ALT" X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N --=====================_60253550==.ALT Content-Type: text/plain; charset="us-ascii"; format=flowed Where do you use the pkchanged that you are setting to 'Y'? In some cases, you can use CASE statement inside INSERT etc too if you want. I am assuming you are trying to execute the triggered insert ONLY IF the Primary Key value of the new row is different than the old row and the PK column is ROLLNO. In general, it could be like this (@see bold): Create trigger APP.TRU_A_STUDENT after update on APP.STUDENT Referencing old as oldRow new as newRow For each Row MODE DB2SQL Insert Into APP.SHADOW_STUDENT(common_id, operationType, status, ROLLNO , NAME , PAY , CLS , old_ROLLNO , server_name ) SELECT * FROM (Values ((Select max(cid) from TempTable) , 'U' , 'A' , newRow.ROLLNO , newRow.NAME , newRow.PAY , newRow.CLS , oldRow.ROLLNO , 'sube_3001')) as t WHERE (newRow.ROLLNO<>oldRow.ROLLNO) I don't have your db schema, so I did not test, but you get the idea: When you append the where clause, the select returns nothing, and you insert nothing if PK has not changed. VALUES syntax when used inside a FROM clause is as follows: select * from (VALUES(1)) as temp Excuse my html formatting if it causes trouble. Regards, Suavi At 11:27 PM 8/11/2005, you wrote: >Hi, > >Lot of thanks for your reply. I have the following trigger. > > >Create trigger APP.TRU_A_STUDENT >after update on APP.STUDENT >Referencing old as oldRow new as newRow For each Row MODE DB2SQL >Insert Into APP.SHADOW_STUDENT(common_id, operationType, status, >ROLLNO , NAME , PAY , CLS , old_ROLLNO , server_name ) Values ((Select >max(cid) from TempTable) , 'U' , 'A' , newRow.ROLLNO , newRow.NAME , >newRow.PAY , newRow.CLS , oldRow.ROLLNO , 'sube_3001') > > >I would like to add the following statement in trigger before >execution of insert statement : > >delclare pkchanged char(1); >begin >if(newRow.ROLLNO!=oldRow.ROLLNO!=) then >pkchanged ='Y' ; >end if; >end; > >Please reply me I am waiting. > >Thanks and Regrds, >Sube Singh > > > > >On 8/12/05, Ali Demir wrote: > > Conditionally firing the trigger is not supported yet if i remember > correct, > > but you can carry the IF condition into the where clause. > > > > Example: > > > > create trigger S.TRIGNAME > > after update of COL1 on S.T1 > > referencing NEW as N OLD as O > > for each row mode db2sql > > insert into S.T2(COLN) > > select COLX > > from S.T3 > > where (not N.COL1=O.COL1); > > > > > > This will insert nothing if the condition (not N.COL1=O.COL1) evaluates to > > FALSE. [may need to check for NULLs separately if cols are nullable in > these > > things] > > > > It will feel like trigger did not fire. > > > > Regards, > > Suavi > > > > > > > > At 10:03 PM 8/11/2005, you wrote: > > Hi, > > > > Can any one help? I would like to use the IF control statement in row > level > > trigger. I do not find any detail related to it in manual and on web > > site.If any > > have Idea about it please post me a example. > > > > Waiting for your reply. > > > > Thanks and Regrds, > > > > Sube Singh --=====================_60253550==.ALT Content-Type: text/html; charset="us-ascii" Where do you use the pkchanged that you are setting to 'Y'? In some cases, you can use CASE statement inside INSERT etc too if you want. I am assuming you are trying to execute the triggered insert ONLY IF the Primary Key value of the new row is different than the old row and the PK column is ROLLNO.

In general, it could be like this (@see bold):

Create trigger APP.TRU_A_STUDENT
after update on APP.STUDENT
Referencing old as oldRow new as newRow For each Row MODE DB2SQL
Insert Into APP.SHADOW_STUDENT(common_id, operationType, status,
ROLLNO , NAME , PAY , CLS , old_ROLLNO , server_name ) SELECT * FROM (Values ((Select
max(cid) from  TempTable) , 'U' , 'A' , newRow.ROLLNO , newRow.NAME ,
newRow.PAY , newRow.CLS , oldRow.ROLLNO , 'sube_3001')) as t WHERE (newRow.ROLLNO<>oldRow.ROLLNO)

I don't have your db schema, so I did not test, but you get the idea: When you append the where clause, the select returns nothing, and you insert nothing if PK has not changed.

VALUES syntax when used inside a FROM clause is as follows:

select * from (VALUES(1)) as temp

Excuse my html formatting if it causes trouble.

Regards,
Suavi

At 11:27 PM 8/11/2005, you wrote:
Hi,

Lot of thanks for your reply. I have the following trigger.   


Create trigger APP.TRU_A_STUDENT
after update on APP.STUDENT
Referencing old as oldRow new as newRow For each Row MODE DB2SQL
Insert Into APP.SHADOW_STUDENT(common_id, operationType, status,
ROLLNO , NAME , PAY , CLS , old_ROLLNO , server_name ) Values ((Select
max(cid) from  TempTable) , 'U' , 'A' , newRow.ROLLNO , newRow.NAME ,
newRow.PAY , newRow.CLS , oldRow.ROLLNO , 'sube_3001')


I would like to add the following statement in trigger before
execution of insert statement :

delclare pkchanged char(1);
begin
if(newRow.ROLLNO!=oldRow.ROLLNO!=) then
pkchanged ='Y' ;
end if;
end;

Please reply me I am waiting.

Thanks and Regrds,
Sube Singh




On 8/12/05, Ali Demir <demir4@yahoo.com> wrote:
> Conditionally firing the trigger is not supported yet if i remember correct,
> but you can carry the IF condition into the where clause.
>
> Example:
>
> create trigger S.TRIGNAME
> after update of COL1 on S.T1
> referencing NEW as N OLD as O
> for each row mode db2sql 
> insert into S.T2(COLN) 
> select COLX 
> from S.T3
> where (not N.COL1=O.COL1);
>
>
> This will insert nothing if the condition (not N.COL1=O.COL1) evaluates to
> FALSE. [may need to check for NULLs separately if cols are nullable in these
> things]
>
> It will feel like trigger did not fire.
>
> Regards,
> Suavi
>
>
>
> At 10:03 PM 8/11/2005, you wrote:
> Hi,
>
> Can any one help? I would like to use the IF control statement in row level
> trigger. I do not find any detail related to it in manual and on web
> site.If any
> have Idea about it please post me a example.
>
> Waiting for your reply.
>
> Thanks and  Regrds,
>
> Sube Singh
--=====================_60253550==.ALT--