Re: check the duplicate code

Dear ychin,

I think database will not violate this rule in trigger even if you 
issue multiple insert statements simultaneously. B'cos trigger 
will execute for each row and after running the code in the 
trigger, it will actually insert the first row and then for the 
next statement it will give you count as 1 as never as 0.

hope it helps you clarify...

Avinash



On Mon, 02 Dec 2002 Loh Wai Chin wrote :
> 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: c.r.farmer@xxxxxxxxxxxxxxxx 
> [mailto:c.r.farmer@xxxxxxxxxxxxxxxx]
>         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"
> 	                      <wcloh@xxxxxxxxxx        To:       
> "Oracle" <oracle@xxxxxxxxxxxx>
>                               >                        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.unsub%%.


________________________________________________________________
 NIIT supports World Computer Literacy Day on 2nd December.
 Enroll for NIIT SWIFT Jyoti till 2nd December for only Rs. 749
 and get free Indian Languages Office software worth Rs. 2500.
 For details contact your nearest NIIT centre, SWIFT Point
 or click here http://swift.rediff.com/




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