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