Re: check the duplicate code

If you can change value of del field to:
(Null = deleted, 0(or any not nul value) = not
deleted)
then you can create unique key constraint for code and
department look:
ALTER TABLE QTC_OWNER.department ADD CONSTRAINT A
  UNIQUE (
  code
  ,del
)
/

--- loh wai chin <wcloh@xxxxxxxxxx> wrote:
> 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%%.


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to leave-oracle-1796914O@xxxxxxxxxxxxx