Use packages.

Check out this link

Why can't you use a unique constraint?

Cheers,

Colin

Using the AUTONOMOUS TRANSACTION pragma in the trigger's PL/SQL block is one way to got - probably the easiest - but has some considerations.

Under just the wrong conditions it can cause data issues. With luck you won't encounter these conditions, though. There are complete write-ups on this issue on-line, and particuarly in Tom Kyte's web site. In particular you need to know that if you do use ATs you can't see any uncommited changes on the trigger table

This will be no use in a multi-user environment.

Cheers, APC

The autonomous transaction approach is flawed in two ways. Yes it will not show you any uncommitted changes in other sessions, but that just results in ugly exceptions when we come to commit. More importantly it won't include any changes in out own session...

SQL> create or replace trigger trg1 before insert on t1

  2  for each row

  3  declare

  4    n pls_integer;

  5    pragma autonomous_transaction;

  6  begin

  7    select count(*) into n from t1

  8    where col1 = :NEW.col1;

  9    if n > 0 then raise_application_error(-20001, 'Problem!');

 10    end if;

 11    commit;

 12  end;

 13  /



Trigger created.



SQL> insert into t1 values (42, 'JJJJ')

  2  /



1 row created.



SQL> insert into t1 values (42, 'JJJJ')

  2  /



1 row created.



SQL> insert into t1 values (42, 'JJJJ')

  2  /



1 row created.



SQL> select * from  t1 where col1 = 42

  2  /

      COL1 COL2


----------

        42 JJJJ

        42 JJJJ

        42 JJJJ



SQL> commit;



Commit complete.



SQL> delete from t1 where col1 = 42

  2  /



3 rows deleted.



SQL> insert into t1 values (42, 'JJJJ')

  2  /

insert into t1 values (42, 'JJJJ')

*

ERROR at line 1:

ORA-20001: Problem!

ORA-06512: at "A.TRG1", line 7

ORA-04088: error during execution of trigger 'A.TRG1'


SQL> 

Cheers, APC

Create a function with Pragma Autonomous _Transaction and check for the newly inserted values by passing :new values frmo the trigger body to the Function if found then handle exceptions else insert into table based on this :new value passed.

Fnord. What difference would that make? Precisely none. The whole point about autonomous transactions are that they are independent of the current transaction.

This does not just mean that our current transaction is isolated from commits or rollbacks in the autonomous transaction. It also means that any queries in the autonomous transaction cannot see the changes in the main transaction. Think of an autonomous transaction as opening up a new session: the exact same data consistency rules apply.

Cheers, APC