Hello everybody,

I keep seeing a common missconception apearing, from time to time, in this
kind of forums. It is that one could enforce uniqueness of one or more
fields in a table with triggers instead of a unique key.

It isn't just a bad practice is is a really a dangerous thing ! Let me show
why:

Consider a case when, for simplicity, we forbid any update and we check the
uniqueness with a before insert trigger.

Let's say that the trigger tries to find a row in the table with same values
in the "key fields" as the one that triggered the insert and if it does it
raises an exception.

At a first look it apears that everything is OK, we enforced uniqueness. BUT
THIS IS ONLY AN IMPRESSION if the trigger is running inside a transaction
other than "dirty read" one !
Consider the following scenario:
- the table is "TABLE" with the field we are trying to make unique named
"ID"
- the following transactions are other than "dirty read" ones

1. start transaction "A"
2. insert into TABLE (ID) values (1);
3. start transaction "B"
4. insert into TABLE (ID) values (1);
/* at this time transaction "A" is not commited yet, so the row created
by the first insert
isn't visible in transaction "B", so our trigger will NOT raise the
exception !!! */
5. commit transaction "A"
6. commit transaction "B"

After that our "TABLE" contains two records with the same value for the ID
field.
QED.

It might be usefull to put a chapter in docs with this type of
missconceptions
(printed in uppercase :-).
But this is up to FB gurus !

Regards !
Raul.



-------------------------------------------------------
Xnet scaneaza automat toate mesajele impotriva virusilor folosind RAV AntiVirus.
Xnet automatically scans all messages for viruses using RAV AntiVirus.

Nota: RAV AntiVirus poate sa nu detecteze toti virusii noi sau toate variantele lor. Va rugam sa luati in considerare ca exista un risc de fiecare data cand deschideti fisiere atasate si ca MobiFon nu este responsabila pentru nici un prejudiciu cauzat de virusi.
Disclaimer: RAV AntiVirus may not be able to detect all new viruses and variants. Please be aware that there is a risk involved whenever opening e-mail attachments to your computer and that MobiFon is not responsible for any damages caused by viruses.