Re: trigger question

Hi Bjørn

Thanks for the feedback.

Yes I know I can't insert a duplicate, but I wanted a "silent skip" (if you catch my meaning). I am using an sql*loader process (actually external table as source and insert all sql statement) that can just throws data into some tables. I wanted the trigger to "ignore" duplicates *without* throwing an exception/error only inserting new values. Perhaps I should use "merge into" instead.

Cheers
Tony

• Original Message ----- From: "Bjørn Dörr Jensen" <B.D.Jensen_at_gmx.net> To: <adolph.tony_at_tiscali.de>; "ORACLE-L" <oracle-l_at_freelists.org> Sent: Sunday, November 27, 2005 9:26 PM Subject: Re: trigger question

> Hi!
> You have an pk on the combination (agentid, tel) - so why the trigger?
> The pk will ensure that you can't insert an duplicate.
> Greetings
> Bjørn
> ----- Original Message -----
> From: "NEW pop.tiscali.de" <adolph.tony_at_tiscali.de>
> To: "ORACLE-L" <oracle-l_at_freelists.org>
> Sent: Sunday, November 27, 2005 2:48 PM
> Subject: trigger question
>
>
> > Hi all,
> >
> > I've been playing around with before insert for each row triggers and
> > trying
> > to create a "skip insert if duplicate".
> >
> > I've tried serveral things, but here's a template:
> >
> > create table agentTel (AgentID number, tel varchar2(30), saved date,
> > constraint pk_agentTel primary key (AgentID, tel))
> > organization index;
> >
> > create or replace trigger t_agentTel_bi
> > before insert or update on agentTel for each row
> > declare
> > dup_entry_exception exception;
> > c number;
> > begin
> > if inserting then
> > select count(*) into c from agentTel
> > where AgentID = :new.AgentID and tel = :new.tel;
> > if c > 0 then
> > raise dup_entry_exception;
> > end if;
> > end if;
> > select sysdate into :new.saved from dual;
> > exception
> > when dup_entry_exception then
> > raise_application_error(-20000,
> > 'somehow stop the rest of the insert here',false);
> > end;
> > /
> >
> > tony_at_DB1> insert into agentTel (AgentID, tel) values (1, 1234);
> >
> > 1 row created.
> >
> > tony_at_DB1> select * from agentTel;
> >
> > AGENTID TEL SAVED
> > ---------- ------------------------------ -----------------------
> > 1 1234 27-Nov-05 14:38:12
> >
> > 1 row selected.
> >
> > tony_at_DB1> insert into agentTel (AgentID, tel) values (1, 1234);
> > insert into agentTel (AgentID, tel) values (1, 1234)
> > *
> > ERROR at line 1:
> > ORA-20000: somehow stop the rest of the insert here
> > ORA-06512: at "TONY.T_AGENTTEL_BI", line 15
> > ORA-04088: error during execution of trigger 'TONY.T_AGENTTEL_BI'
> >
> > I'd like for the duplicate row to be thrown away, i.e. somehow in the
> > before
> > insert trigger to discover the duplicate row and abort the rest of the
> > insert. Is this possible?
> >
> > I can work around this problem by wrapping the inserts with an sp, but
am
> > interested to hear if its possible.
> >
> > Cheers
> > Tony
> >
> >
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
>

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