Re: check the duplicate code

something like this may work
i'm not sure if the syntax is correct because i havent tested it
but hopefully you'll get the idea.


create or replace trigger b4_ins_dept  before insert on tbl_department as
      x number;
begin
      select count(1)
      into x
      from tbl_department
      where code = :new.code
      and del = 0;

      if x != 0   then
            raise_application_error(-20000, 'Cant insert record Duplicates
exist');
      end if;
end;







                                                                                                 
                      "loh wai chin"                                                             
                      <[email protected]        To:       "Oracle" <[email protected]>          
                      >                        cc:                                               
                                               Subject:  [oracle] check the duplicate code       
                      02/12/2002 10:34                                                           
                      Please respond to                                                          
                      "Oracle"                                                                   
                                                                                                 
                                                                                                 




is there a way to prevent inserting a record that with 1 of the fields is
dusplicate with the exsiting record.

i mean lets consider the following situation:

i have 1 table tbl_department

description

id    sys_guid()         (primary key)
code  nvarchar2(10)
del   number(1)

* the field tbl_department.del is to indicate whether the record is
already deleted or not. (1 = deleted, 0 = not deleted)
* tbl_department.code is not a unique key


let's said there is already a department record exist:
id                       code        del
==                       ====        ===
999....                        abc         0


im facing a problem that the system is able to accept another record with
the same tbl_department.code = abc which i try to prevent it.


is there a way (for example, create a trigger) that i can check the
duplicate code before inserting into the table?


Thanks a lot and regards,
ychin
---
Change your mail options at http://p2p.wrox.com/manager.asp or
to unsubscribe send a blank email to %%email.unsub%%.






---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].