Re: conditional unique index to restrict duplicates of a particular set

  • From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
  • To: Carlos Sierra <carlos.sierra.usa@xxxxxxxxx>
  • Date: Wed, 7 May 2014 20:55:57 +0200

Hi Carlos,

I am sorry I have not been clear with my first answer. I was thinking
exactly to what Mike and Noris have already noticed. Two concurrent users
inserting nearly at the same time the same unique key, they (their
corresponding trigger) will not see the insert each other has just inserted
because no one has committed at the time of the check. If we really want to
do that with an AFTER trigger then we need to serialize the access to the
table. And this is what I was meaning by "it will not scale"

Cheers

Mohamed Houri
www.hourim.wordpress.com



2014-05-07 17:32 GMT+02:00 Carlos Sierra <carlos.sierra.usa@xxxxxxxxx>:

> Mohamed,
>
> Would you please elaborate why an INSERT/UPDATE trigger using a non-unique
> index on those 4 columns would not scale? The trigger would do an index
> lookup using the 4 columns when the set of values passed is expected to be
> unique and raise an error if there exists such set. This method would be
> clean, and the index could be used by other queries.
>
> Cheers — Carlos
>
>
> On May 7, 2014, at 9:50 AM, Mohamed Houri <mohamed.houri@xxxxxxxxx> wrote:
>
> I am wondering how you will be able to enforce uniqueness via a trigger in
> a multi-user concurent application? It will not scale. You need either to
> use your unique function based index or, if in 11gR1 and up create a
> virtual column and place a unique constraint (unique index) on this virtual
> column
>
> Best regards
> Mohamed Houri
>
>
> 2014-05-07 15:13 GMT+02:00 Fergal Taheny <ftaheny@xxxxxxxxx>:
>
>> Hi Carlos,
>>
>> Won't you face a mutating table error if you try to use a trigger for
>> this.
>>
>> Regards,
>> Fergal
>>
>
>
>
> --
> Bien Respectueusement
> Mohamed Houri
>
>
>


-- 
Bien Respectueusement
Mohamed Houri

Other related posts: