Having problem with db2 trigger

huzefaq
huzefaq used Ask the Experts™
on
Hi guys

I have at trigger. What i need is that if I update the price column in the offerprice table it should place the old value of price in the compareprice column only in that row. But I am having the following problem with the trigger I have. i would really appreciate any help regarding this


CREATE TRIGGER mytrig    
after  UPDATE OF price          
ON offerprice
REFERENCING NEW AS N OLD as O
FOR EACH ROW
BEGIN ATOMIC
  update offerprice op set op.compareprice = O.price;
END

Then, when I updated the price column one row, it updated the offerprice column in the entire table.

e.g.
select * from offerprice

PRICE    COMPAREPRICE         OFFERID
   50                          -              1
   10                          -              2
   15                          -              3

update offerprice
set price = 20        
where offerid = 3      

select * from offerprice

PRICE    COMPAREPRICE         OFFERID
   50                       15               1
   10                       15               2
   20                       15               3
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Developer / Database Administrator
Commented:

Greetings,

As I said in your previous question, the AFTER UPDATE trigger only works if you use the WHERE clause in the update in the trigger.

e.g.
CREATE TRIGGER mytrig    
after  UPDATE OF price        
ON offerprice            
REFERENCING NEW AS N OLD as O  
FOR EACH ROW
BEGIN ATOMIC
  update offerprice op  
  set op.compareprice = O.price
  where op.offerid = o.offerid;
END

But, for clarity and performance reasons, I'd make it a BEFORE UPDATE trigger.
e.g.
CREATE TRIGGER mytrig    
before UPDATE OF price        
ON offerprice            
REFERENCING NEW AS N OLD as O  
FOR EACH ROW                  
BEGIN ATOMIC                  
  set n.compareprice = O.price;
END

HTH,
DaveSlash

Author

Commented:
hey davelash

I tried thsi trigger

CREATE TRIGGER mytrig    
after  UPDATE OF price        
ON offerprice            
REFERENCING NEW AS N OLD as O  
FOR EACH ROW
BEGIN ATOMIC
  update offerprice op  
  set op.compareprice = O.price
  where op.offerid = o.offerid;
END

but I am getting this error

An unexpected token "END-OF-STATEMENT" was found following ".offerid = O.offerid".  Expected tokens may include:  "<delim_semicolon>".

Would you know why I am getting this error

Dave FordSoftware Developer / Database Administrator

Commented:

I don't know, my friend. It works fine for me. (DB2 for iSeries 5.3)

Try putting a semicolon at the end of the trigger.

-- DaveSlash

Author

Commented:
Actually Daveslash i worked after i remove the begin Atomic  and END tags. But I don'tunderstand why it was the problem.

Dave FordSoftware Developer / Database Administrator

Commented:

Weird, eh?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial