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