Re: SQL design pattern for a delta trigger?

Ted Byers wrote:
> --- Erik Jones <erik@myemma.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.

You can do this with a conditional.  Something like the following should 
work.

IF
   NOT (a query matching your data returns rows)
THEN
   INSERT (your new data)

AFAIK, the developers are working on implementing the {MERGE, UPDATE OR 
ON FAILURE INSERT, UPSERT} statement.  Until then, you have to build 
your upsert manually.

Colin