Make the ‘Association Number’ field unique

Hello, it seems to be a CRIM 

I would agree with Aude. You can create a Custom Event triggered on Creation or modification of a Customer, with an Event Action of type “Execute online SQL”, with code to check if there are any pre-existing customers with association number matching the new association number, and if so then display an error message (and therefore prevent the new customer from being created).

Maybe something like this (untested)…

Declare
PRAGMA AUTONOMOUS_TRANSACTION;

Var_Company_ID      Varchar2(50) := '';

cursor DuplicationCheck is
select CUSTOMER_ID
  from CUSTOMER_INFO
 where ASSOCIATION_NO = '&NEW:ASSOCIATION_NO'

begin
  open DuplicationCheck;
    fetch DuplicationCheck into Var_Company_ID;
  close DuplicationCheck;

  if var_Company_ID is not null
  then Error_SYS.Record_General('DUPLICATION ERROR',' '||'&NEW:ASSOCIATION_NO'||
                                ' is already assigned to Customer: '||Var_Company_ID||
                                ' (The Association Number must be unique).');
  end if;
end;

• Robin