Re: trigger question

Hi all,

Thanks for the replies,

I've given up on my "error skipping trigger" and gone for a combination of
INSERT ALLs and MERGE INTOs using an external table as the source.  I guess
this would be the performant solution anyway. (if it mattered)

Thanks again for the feedback,
Cheers
Tony

PS Norman:  I tried the exception handling you suggested (amoung other
things), but as you've how dicovered (after the second cup of coffee), it
will raises an exception further up the stack.
----- Original Message ----- 
From: "malcolm arnold" <malcolmarnold@xxxxxxxxx>
To: <norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx>; <adolph.tony@xxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, November 28, 2005 11:20 AM
Subject: Re: trigger question


On 28/11/05, Norman Dunbar <norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
> Morning Tony,
>
> I notice from a few of your subsequent postings that you got some
> replies - I didn't see them, so apologies if this is duplicating
> information you already have.
>
> In your trigger error handling code, do this :
>
>
> exception
>  when dup_entry_exception then
>  raise_application_error(-20000,
>   NULL;
>  when others then
>   raise;
>
> Shove the above into your exvception trigger and it will not raise an
> exception when it detects a duplicate and the duplicate row will not be
> inserted. However, if another error occurs, it will be raised. You could
> leave out the 'when others' clause and the same would occur - I like to
> be explicit.
>

Norman,

Are you sure about this?  If you handle the dup_entry_exception the
trigger won't fail and the row will be inserted.  I don't think there
is any way that an after insert trigger can silently prevent the
insert from occuring.

Tony,

I believe the best thing to do would be to do the load then remove the
duplicates with one delete statement afterwards.

Malcolm.

--
http://www.freelists.org/webpage/oracle-l

Other related posts: