asked on

Having problem with db2 trigger

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

huzefaq

ASKER

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

Member_2_2484401

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

huzefaq

ASKER

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

Member_2_2484401

Weird, eh?