Solved

Strange Results with Oracle  Trigger

Posted on 2004-09-26
6
1,144 Views
Last Modified: 2008-01-09
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
Comment
Question by:azsat
6 Comments
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 150 total points
Comment Utility
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
 
LVL 2

Assisted Solution

by:pek99
pek99 earned 150 total points
Comment Utility
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
 
LVL 9

Accepted Solution

by:
pratikroy earned 200 total points
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:azsat
Comment Utility
Gents,

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

0
 
LVL 2

Expert Comment

by:pek99
Comment Utility
You are lucky. But I thought you've done that BEFORE writing anything here ;-)))
0
 
LVL 9

Expert Comment

by:pratikroy
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now