Solved

Strange Results with Oracle  Trigger

Posted on 2004-09-26
6
1,148 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…

749 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