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