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_at_gmail.com> To: <norman.dunbar_at_environment-agency.gov.uk>; <adolph.tony_at_tiscali.de> Cc: <oracle-l_at_freelists.org>
Sent: Monday, November 28, 2005 11:20 AM Subject: Re: trigger question

On 28/11/05, Norman Dunbar <norman.dunbar_at_environment-agency.gov.uk> 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