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

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_at_gmail.com>:

> 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_at_gmail.com> 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_at_gmail.com>:
>
>> 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

--
http://www.freelists.org/webpage/oracle-l