db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From sube singh <sube.si...@gmail.com>
Subject Re: Use of If control statement in row level trigger
Date Sat, 13 Aug 2005 04:08:53 GMT
Thanks
 Appreciate your support and pormpt reply
 Sube Singh

 On 8/12/05, Satheesh Bandaram <satheesh@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 <demir4@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
> > 
> > 
>

Mime
View raw message