troubleshooting Question

Having problem with db2 trigger

Avatar of huzefaq
huzefaq asked on
DatabasesDB2
5 Comments1 Solution954 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Member_2_2484401

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros