Solved

Strange Results with Oracle  Trigger

Posted on 2004-09-26
6
1,145 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
ID: 12156300
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
ID: 12156575
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
ID: 12160790
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
ID: 12173239
Gents,

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

0
 
LVL 2

Expert Comment

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

Expert Comment

by:pratikroy
ID: 12179064
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

895 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

15 Experts available now in Live!

Get 1:1 Help Now