db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ali Demir <dem...@yahoo.com>
Subject Re: Use of If control statement in row level trigger
Date Fri, 12 Aug 2005 06:58:33 GMT
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

Mime
View raw message