Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: trigger question

Re: trigger question

From: NEW pop.tiscali.de <adolph.tony_at_tiscali.de>
Date: Sun, 27 Nov 2005 22:49:20 +0100
Message-ID: <006301c5f39c$6ccd0370$6401a8c0@tonypc>


Hi Paul,

Yes I know its an overhead, but I'm not inserting too much and I'm only playing about, so not too worried about performance. Really just experimenting with features I've not used before and/or new features.

I think I'll use "merge into ... when not matched ... using my external table"

Thanks for the feedback
Cheers
Tony

On 11/27/05, NEW pop.tiscali.de <adolph.tony_at_tiscali.de> wrote:
> 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

Tony,

You want to have a trigger fire for each row while using sql*loader? I wouldn't want any triggers to fire for a table while using sql*loader - that's just way too much overhead.

Paul

>
>
> ----- 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
>
>
>

--
#/etc/init.d/init.cssd stop
-- play a Sony CD, install a rootkit today

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Nov 27 2005 - 15:56:12 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US