Re: trigger question

  • From: "NEW pop.tiscali.de" <adolph.tony@xxxxxxxxxx>
  • To: Bjørn Dörr Jensen <B.D.Jensen@xxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 27 Nov 2005 22:27:38 +0100

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@xxxxxxx>
To: <adolph.tony@xxxxxxxxxx>; "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxx>
> To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
> 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@DB1> insert into agentTel (AgentID, tel) values (1, 1234);
> >
> > 1 row created.
> >
> > tony@DB1> select * from agentTel;
> >
> >   AGENTID TEL                            SAVED
> > ---------- ------------------------------ -----------------------
> >         1 1234                           27-Nov-05 14:38:12
> >
> > 1 row selected.
> >
> > tony@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
> >
> >
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
>

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


Other related posts: