Help with a trigger

[James Foreman]

[8.2.1 UDB on linux]

I've got two tables, one holding the current price of a product, and
one to hold all prior versions;

CREATE TABLE product_pricing (product varchar(10), price decimal (9,
2), last_changed date);
CREATE TABLE product_pricing_history (product varchar(10), price
decimal (9, 2), start date, end date);

Every time update the price in the product_pricing table, I want the
old value to be inserted into the product_pricing_history table. So I
thought this would work:

CREATE TRIGGER price_history_tr
NO CASCADE BEFORE UPDATE OF price ON product_pricing
REFERENCING OLD as oldprice
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO product_pricing_history (product, start, end, price)
VALUES (oldprice.product, oldprice.last_changed, current date,
oldprice.price);
END

However, I get back SQLSTATE 42987: The statement is not allowed in a
procedure or trigger.

Am I doing something blatantly wrong here, or should I take this up
with IBM Support? Being new to triggers, I'm working from the online
doco, and I can't see what in particular I might be doing that's wrong
(
http://publib.boulder.ibm.com/infoce...n/r0000931.htm
)

[Benjamin Gufler]

Hi James,

James Foreman wrote:

CREATE TRIGGER price_history_tr
NO CASCADE BEFORE UPDATE OF price ON product_pricing
REFERENCING OLD as oldprice
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO product_pricing_history (product, start, end, price)
VALUES (oldprice.product, oldprice.last_changed, current date,
oldprice.price);
END

However, I get back SQLSTATE 42987: The statement is not allowed in a
procedure or trigger.

Afair, inserting data in other tables is not allowed in DB2 before
triggers. Try using an "after update" trigger, that should work.

hth,
Benjamin
--
11.+12.11.2005: SFSCon 2005
see http://www.sfscon.it

[James Conrad StJohn Foreman]

Thanks for that - all sorted