Solved

Insert Trigger Abort

Posted on 1998-12-09
8
1,657 Views
Last Modified: 2008-02-20
I have a trigger which fires before an insert, for every row.

I check for a certain range of values in a certain column.
If the data is outside this range, I wish to cancel the insert FOR THAT ROW ONLY.
Can this be done and if so, how?

I've tried raise_application_error, but this aborts the entire insert - every row.

Thanks for any help
0
Comment
Question by:rderidder
8 Comments
 
LVL 2

Expert Comment

by:dslavin
ID: 1082991
I believe that you can create your own exception and raise that without aborting other inserts.  If you raise your own exception, then you can even have it write information to a file or table detailing the failure for further analysis.

Here is how you would create your own exception:

DECLARE
    <declare variables>
   
-- This creates the exception
    out_of_range                  EXCEPTION;

-- This associates an error number (< -20000) with your exception
    PRAGMA EXCEPTION_INIT (out_of_range, -20001);

BEGIN

   if (insert record out of range) then
      raise out_of_range;

EXCEPTION
  WHEN out_of_range THEN
-- This will do nothing
     null;

OR
-- This might just send you a message, or it might cause the other inserts to fail as well
    raise_application_error (-20001, 'ERROR: Inserted value out of range.');

END;
0
 

Author Comment

by:rderidder
ID: 1082992
Raising any unhandled exception causes the entire insert to abort.
Handling an exception (even one I declare), will cause the insert to still go through.
As far as I can gather, exceptions are not the answer.

It seems I will have to resort to deleting the inserted records with an AFTER trigger.
0
 

Expert Comment

by:jigyasa
ID: 1082993
Why don't you try doing the same using the column constraint. As I understand this can be directly achieved using column constraints.
0
 

Author Comment

by:rderidder
ID: 1082994
Does a constraint not cancel the entire insertion?
If I try to insert a million rows, and one fails the contraint, none of the million rows will be inserted. Or am I wrong?

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.

 

Expert Comment

by:clydes
ID: 1082995
How are you handling your commits.  Are you doing a commit for each row or are you doing a commit when you exit the routine.  If you are doing a commit for each row, then your exception that you create for the out of range can do a rollback and only that row will be canceled.  The when and where you do your commits and rollbacks are very important.  Does any other records being input depend upon the record you want to rollback.  Check these out.  But the best way of handling bad information is to do it in a trigger like you have. Just find out how you are doing your commits.
0
 
LVL 9

Expert Comment

by:konektor
ID: 1082996
I didn't try but I thing that 'jigyasa Date: Thursday, December 17 1998 - 11:33AM PST' is right with column constraint but you cannot use insert into .... (select ... from ..... ), because one violation of column constraint aborts all inserts, solution is, that you use cursor in procedure and then in body of procedure call insert - exception :
create procedure
cursor xx_cur is
  select
begin
  for xx_rec in xx_cur loop
    begin
      insert into ... values (xx_cur.col1....)
      exception
         when violated column constraint .....
           null;
    end;
  end loop;
end;
/
0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 50 total points
ID: 1082997
Do you have any control over the process that is creating the inserts?  A data base trigger may be the most reliable way to do data entry validation, but it is certainly not the most elegant or user-friendly.  Instead of raising an exception, you could have the pre-insert trigger set an error flag in the record (you may need to add a column to the table for this).  This will not prevent the row from being loaded, but it would make it easy to find, especially if you create an index on the error flag column and make sure that it is null for all valid records.  Another option might be an after-insert trigger, but again not if it raises exceptions.  At best it could move the record to an error table or write it out to an ASCII file using utl_file.  For this approach to work though, you may need the after-insert trigger to be a statement level trigger, rather than a row-level trigger to avoid the "mutating table" problem.  This would require a pre-insert trigger to set an error flag on the problem row(s).
0
 

Author Comment

by:rderidder
ID: 1082998
I've actually decided to fix this problem by changing my inserting program to do the checks.
I'll just accept this since it seems I can't delete a question...

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

707 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