We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Insert Trigger Abort

rderidder
rderidder asked
on
Medium Priority
1,717 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
Comment
Watch Question

Commented:
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;

Author

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.

Commented:
Why don't you try doing the same using the column constraint. As I understand this can be directly achieved using column constraints.

Author

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?

Commented:
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.

Commented:
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;
/
Database Administrator
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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...

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.