Re: trigger question
- From: "NEW pop.tiscali.de" <adolph.tony@xxxxxxxxxx>
- To: "Paul Drake" <bdbafh@xxxxxxxxx>
- Date: Sun, 27 Nov 2005 22:49:20 +0100
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
----- Original Message -----
From: "Paul Drake" <bdbafh@xxxxxxxxx>
To: <adolph.tony@xxxxxxxxxx>
Sent: Sunday, November 27, 2005 10:41 PM
Subject: Re: trigger question
On 11/27/05, NEW pop.tiscali.de <adolph.tony@xxxxxxxxxx> 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@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
>
>
>
--
#/etc/init.d/init.cssd stop
-- play a Sony CD, install a rootkit today
--
//www.freelists.org/webpage/oracle-l
Other related posts:
