Re: Use of If control statement in row level trigger

[Ali Demir]

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 <[EMAIL PROTECTED]> 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