aspose file tools*
The moose likes Oracle/OAS and the fly likes Trigger Function Problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Trigger Function Problem" Watch "Trigger Function Problem" New topic
Author

Trigger Function Problem

M Aslam
Greenhorn

Joined: Jul 12, 2004
Posts: 14
Hi,

I need some urgent help with an oracle trigger.

I have trigger which inserts a row into a Price history table every time there is an insert or delete
on a Price table

The trigger is :

CREATE OR REPLACE TRIGGER PRICE_HIST_TRIG
after update or delete on PRICE
for each row
begin
INSERT INTO PRICE_HIST
VALUES
( ld.price_id,
ld.price,
ld.date,
ld.version
)

The PRICE_HIST table has a unique index on (price_id, date, version).

In my code I test to see if the price_id does not exists, if so, I set the version = 1 and and insert a row into the PRICE table.

If the price exists, I set version = version (of row found) + 1 and attempt to update the PRICE table.

The program is failing on updates.

I start with empty tables, the new inserts go in fine, but if there
is another price for same price_id , quite rightly an update is attempted, this fails on the
unique index for PRICE_HIST. From what I can see the old version should be one and the new version 2 (I've double checked this in the code).

It should be noted that on INSERT no entry is made into PRICE Hist, on update version 1 should have been carried over to the PRICE_HIST table but looks like it was not since the index failure.

Please help!

[edited to disable smilies
to do this yourself, there is a checkbox below the UBB codes]
[ September 26, 2004: Message edited by: Jeanne Boyarsky ]
Jeremy Wilson
Ranch Hand

Joined: Feb 18, 2003
Posts: 166
Can you post the sql statements that you run in sql*plus? This may add more insight.


Jeremy Wilson
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Trigger Function Problem