Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1692
  • Last Modified:

Insert Trigger Abort

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
rderidder
Asked:
rderidder
1 Solution
 
dslavinCommented:
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
 
rderidderAuthor Commented:
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
 
jigyasaCommented:
Why don't you try doing the same using the column constraint. As I understand this can be directly achieved using column constraints.
0
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!

 
rderidderAuthor Commented:
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
 
clydesCommented:
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
 
konektorCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
rderidderAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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