check the duplicate code

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 protected].