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 07:29:53 GMT
There is no such thing as declare variable. Instead you can use something 
like this instead of pkchanged inside your insert statement:

(CASE WHEN new.ROLLNO<>old.ROWNO THEN 'Y' ELSE 'N' END)

Try this to get an idea about how it works:

SELECT (CASE WHEN 1<>0 THEN 'Y' ELSE 'N' END)  as COL1 FROM (VALUES(1)) as t

Also, there is no begin-end around trigger body. You need to have single 
statement inside one trigger. You can have multiple triggers if you need 
multiple statements.

Regards,
Suavi


At 12:08 AM 8/12/2005, you wrote:
>Hi,
>
>I would like to insert the value of pk_changed into the table
>student if old and new primary key value is not same. Plz see the 
>following statement
>
>Create trigger APP.TRU_A_STUDENT
>after update on APP.STUDENT
>Referencing old as oldRow new as newRow For each Row MODE DB2SQL
>delclare pkchanged char(1);
>begin
>if(newRow.ROLLNO!=oldRow.ROLLNO!= ) then
>pkchanged ='Y' ;
>end if;
>end;
>
>Insert Into APP.SHADOW_STUDENT(common_id, operationType, status,
>ROLLNO , NAME , PAY , CLS , old_ROLLNO , server_name ) Values ((Select
>max(cid) from  TempTable,pk_change_cols) , 'U' , 'A' , newRow.ROLLNO , 
>newRow.NAME ,
>newRow.PAY , newRow.CLS , oldRow.ROLLNO , 'sube_3001',pkchanged )
>
>I hope that you will reply me as soon as possible
>
>Thanks ans Regards,
>
>Sube Singh
>
>
>
>
>On 8/12/05, Ali Demir <<mailto:demir4@yahoo.com>demir4@yahoo.com> wrote:
> > 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 <<mailto:demir4@yahoo.com>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