Re: SQL design pattern for a delta trigger?

--- Vivek Khera <khera@kcilink.com> wrote:

> 
> On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:
> 
> > For what it's worth, the real algorithm would be
> as follows.  I  
> > hadn't had enough coffee yet, and I forgot the
> UPDATE bit.
> >
> > IF
> >  (a query matching your old data returns rows)
> > THEN
> >  UPDATE with your new data
> > ELSE
> >  INSERT your new data
> 
> Still exists race condition.  Your race comes from
> testing existence,  
> then creating/modifying data afterwards.  You need
> to make the test/ 
> set atomic else you have race.
> 

Yes, but how do you do that in a stored function or
procedure or in a trigger.  It would be obvious to me
if I were writing this in C++ or Java, but how do you
do it using SQL in an RDBMS?  

I saw something about table locks, but that doesn't
seem wise, WRT performance.

The classic example of a race condition, involving a
bank account, was used in the manual to introduce the
idea of a transaction, but we can't use a transaction
in a trigger, can we?

It is one thing to point out a race condition, but a
pointer to a solution that would work in the context
of the problem at hand would be useful and
appreciated.

Thanks all.

Ted