Re: check the duplicate code

hi,
 
thanks for ur reply.
 
i have tried the trigger, but, if i try to submit 2 record with the same code concurrently, there is still some chance that the database will accept the both record. 2 out of 5 trials will fail. :(
 
why this happened??
 
best regards,
ychin

	-----Original Message----- 
	From: [email protected] [mailto:[email protected]] 
	Sent: Mon Dec/02/2002 6:41 PM 
	To: Oracle 
	Cc: 
	Subject: [oracle] 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.unsub%%.
	



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