You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by sube singh <su...@gmail.com> on 2005/08/12 07:03:54 UTC
Use of If control statement in row level trigger
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
Re: Use of If control statement in row level trigger
Posted by sube singh <su...@gmail.com>.
Thanks
Appreciate your support and pormpt reply
Sube Singh
On 8/12/05, Satheesh Bandaram <sa...@sourcery.org> wrote:
>
> Shouldn't you be using *oldRow *and *newRow *instead of old and new in the
> CASE statement? Like:
> *
> CASE WHEN newRow.ROLLNO!=oldRow.ROWNO THEN 'Y' ELSE null END*
>
> Satheesh
>
> sube singh wrote:
>
> Hello,
> Thanks for prompt reply. I have tried following query
>
> *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.REP_SHADOW_STUDENT ( Rep_common_id, Rep_operationType, Rep_status,
> ROLLNO , NAME , PAY , CLS , rep_old_ROLLNO , Rep_server_name ,
> Rep_PK_Changed ) Values ((Select max(Rep_cid) from Rep_LogTable) , 'U' , 'A'
> , newRow.ROLLNO , newRow.NAME , newRow.PAY , newRow.CLS , oldRow.ROLLNO ,
> 'sube_3001',(CASE WHEN new.ROLLNO!=old.ROWNO THEN 'Y' ELSE null END))*
>
> It give the following error message :
>
> ERROR 42X04: Column 'NEW.ROLLNO' is not in any table in the FROM list or
> it appears within a join specification and is outside the scope of the join
> specification or it appears in a HAVING clause and is not in the GROUP BY
> list. If this is a CREATE or ALTER TABLE statement then ' NEW.ROLLNO' is
> not a column in the target table.
>
> Thanks and Regards,
>
> Sube Singh
>
> **
>
>
> On 8/12/05, Ali Demir <de...@yahoo.com> wrote:
> >
> > 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 < 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 < 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
> >
> >
>
Re: Use of If control statement in row level trigger
Posted by sube singh <su...@gmail.com>.
Hello,
Thanks for prompt reply. I have tried following query
*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.REP_SHADOW_STUDENT ( Rep_common_id, Rep_operationType, Rep_status,
ROLLNO , NAME , PAY , CLS , rep_old_ROLLNO , Rep_server_name ,
Rep_PK_Changed ) Values ((Select max(Rep_cid) from Rep_LogTable) , 'U' , 'A'
, newRow.ROLLNO , newRow.NAME , newRow.PAY , newRow.CLS , oldRow.ROLLNO ,
'sube_3001',(CASE WHEN new.ROLLNO!=old.ROWNO THEN 'Y' ELSE null END))*
It give the following error message :
ERROR 42X04: Column 'NEW.ROLLNO' is not in any table in the FROM list or it
appears within a join specification and is outside the scope of the join
specification or it appears in a HAVING clause and is not in the GROUP BY
list. If this is a CREATE or ALTER TABLE statement then 'NEW.ROLLNO' is not
a column in the target table.
Thanks and Regards,
Sube Singh
**
On 8/12/05, Ali Demir <de...@yahoo.com> wrote:
>
> 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 <de...@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 <de...@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
>
>
Re: Use of If control statement in row level trigger
Posted by Ali Demir <de...@yahoo.com>.
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 <<m...@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 <<m...@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
>
Re: Use of If control statement in row level trigger
Posted by sube singh <su...@gmail.com>.
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 <de...@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 <de...@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
Re: Use of If control statement in row level trigger
Posted by Ali Demir <de...@yahoo.com>.
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 <de...@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
Re: Use of If control statement in row level trigger
Posted by sube singh <su...@gmail.com>.
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 <de...@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
Re: Use of If control statement in row level trigger
Posted by Ali Demir <de...@yahoo.com>.
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