Re: SQL design pattern for a delta trigger?

--- Vivek Khera <khera(at)kcilink(dot)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