Re: SQL design pattern for a delta trigger?

On Dec 10, 2007, at 4:48 PM, Ted Byers wrote:

>
> --- 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.

In a stored procedure you'd just execute the UPDATE and then check
the FOUND variable to see if it found a row to update:

UPDATE table_name SET foo='bar' WHERE id=5;

IF NOT FOUND THEN
INSERT INTO table_name (id, foo) VALUES (5, 'bar');
END IF;

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com