Re: SQL design pattern for a delta trigger?

On Dec 6, 2007, at 2:36 PM, Ted Byers wrote:

> IS there such a thing? I can be the first to consider
> this.

> What I am aiming for is a solution with a couple
> coupled tables, one of which represents state through
> time and the other represents transactions or deltas
> on the state. With one field (a floating point
> number) in the state table (or should I say a number
> for each id field), it obviously has a time interval
> for which it is valid: a start time and an end time.
>
> What I am after is a situation where the moment a
> record is inserted in the deltas table, a trigger
> function first looks to see if the id provided
> presently has a state in the state table. If not,
> then it creates one. Then, the end date for that
> state record gets set to the current time and a new
> record is inserted with the new state (computed by
> applying the delta to the value in the previous record
> for the state), the current date as the start date and
> null for the end date.
>
> This seems like an obvious thing to try, but I am
> floundering a little and am therefore wondering if
> anyone has seen an SQL design pattern that talks about
> this, and an url where I can see such a discussion
> and, better, an example. The first concern is to
> ensure that every record inserted into the deltas
> table is immediately reflected in the state table, and
> the second is that the history of state can be
> reconstructed from a suitable query on the state
> table.
>
> I can do this easily in client code, but isn't this
> the sort of thing best suited to living within the
> database itself?

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.

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