On 4/15/08, Helen Borrie <helebor@...> wrote:

> If your explicit purpose is to allow one transaction to "re-insert" a record that another has recently deleted, then you should know that there is a high potential that the insert will fail from a primary key violation. That's because the *index maintenance* is not under the same transaction control as the record. The index node for a deleted record stays in place for at least as long as it takes for the deleted *record* to be garbage-collected.

For this case it's good to think about "auto-increment" trigger
without line "if new.xxx is null then". Then you get always new value
from generator (but, of course, can bring another problems).

--
Jiri {x2} Cincura (CTO x2develop.com)
http://blog.vyvojar.cz/jirka/ | http://www.ID3renamer.com