Re: SQL design pattern for a delta trigger?

On Dec 7, 2007, at 8:29 AM, Ted Byers wrote:

>
> --- Erik Jones <erik(at)myemma(dot)com> wrote:
>
>>
>> On Dec 6, 2007, at 2:36 PM, Ted Byers wrote:
>>
>> [snip]
>> What you want to do here for handling the update v.
>> insert is called
>> an "UPSERT". Basically, what you do is run the
>> update as if the row
>> exists and catch the exception that is thrown if it
>> doesn't at which
>> point you insert the record with the end date =
>> now(). After that
>> you can proceed normally with creating the new
>> record with start date
>> = now() and end date = NULL.
>>
> Thanks Eric. Do you know of an URL where this is
> discussed or where I can find an example. None of my
> books discuss this, and my search using google has so
> far produced only noise.
>
> Thanks again.

The basic pseudo-code (not really SQL) is something like:

TRY:
run UPDATE
EXCEPT not found:
make INSERT

If you can give me some table layouts I can probably work out a
better example for you.

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