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