• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1156
  • Last Modified:

Strange Results with Oracle Trigger

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
( :old.price_id,
  :old.price,
  :old.date,
 :old.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!


0
azsat
Asked:
azsat
3 Solutions
 
seazodiacCommented:
try to change your whole trigger definition to this (think you skipped some code of yours)


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

exception when others then
   
INSERT INTO PRICE_HIST
VALUES
( :old.price_id,
 :old.price,
 :old.date,
:old.version+1
)

end;
/

0
 
pek99Commented:
What about splitting this mess into two triggers:

CREATE OR REPLACE TRIGGER PRICE_DEL_HIST_TRIG
after delete
for each row
begin
INSERT INTO PRICE_HIST
VALUES
( :old.price_id,
  :old.price,
  :old.date,
  :old.version + 1
)
end;
/

CREATE OR REPLACE TRIGGER PRICE_HIST_TRIG
before update on PRICE
for each row
begin
:new.version = :old.version +1;
INSERT INTO PRICE_HIST
VALUES
( :old.price_id,
  :old.price,
  :old.date,
  :new.version
)
end;
/
0
 
pratikroyCommented:
I agree with seazodiac. Try to re-think on what you want. Usually in the History tables, you just insert the records and never update the records. This is done, mainly to find out the changes that happend to a record in past. You would be able to see the record (in its entirity) if you select all the records for the combination of price_id, date.

Do you really need the version number or you have this column, just to ensure that a unique record is kept in the PRICE_HIST table.

I would suggest that you have an Oracle SEQUENCE.


If you really need to update the record (and not have just the inserts into the PRICE_HIST and you are sure about it), then you will have to use the EXCEPTION handling within your trigger.

something like :
BEGIN
INSERT INTO PRICE_HIST ....

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
UPDATE PRICE_HIST ...

END;

Alternatively you could have an UPDATE on PRICE_HIST, and check the number of records that are updated. If none, then insert a record. Something like :
BEGIN
UPDATE PRICE_HIST SET ...., version_no = version_no + 1 WHERE ....;

IF SQL%NOTFOUND THEN
INSERT INTO PRICE_HIST ..... values (.... ); -- Insert 1 for version number here
END IF;

END;

You could also try a MERGE statement to ensure that you could INSERT or UPDATE based on the condition/availability of records.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
azsatAuthor Commented:
Gents,

Just by dropping and re-creating the trigger the problem went away.

0
 
pek99Commented:
You are lucky. But I thought you've done that BEFORE writing anything here ;-)))
0
 
pratikroyCommented:
Well there has to be more than that. When you use CREATE OR REPLACE TRIGGER, it will recreate the trigger anyways, if the trigger already exists.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now