Re: fail-safe sql update triggers

On Freitag, 3. September 2010 Rob Richardson wrote:
> Your solution maintains an exact copy of two tables. Whenever a
> record is updated in the first, it is updated in the second, and you
> have lost information about the previous value.

I have a table "filter" where the real work is done. Data in there is
needed up to 30 hours, the table growing quickly. We only need to keep
the last state of a record, not all it's revisions. When the record is
not needed anymore (can be within one minute after inserting also) it
should be deleted out of the "filter" table, but kept in "old_filter".
We also need the data to be immediately in the "old_filter" table, so we
can't simply copy the record on delete.

What I do now in the function is
IF we are UPDATEing a record: DELETE from old_filter; ENDIF
INSERT INTO old_filter SELECT NEW.*;

So when an update occurred on filter, I simply delete and insert the
record. That's OK, but an UPDATE would be better for performance. But
there's no easy and quick method to do that, right?

--
mit freundlichen Grüssen,
Michael Monnerie, Ing. BSc

it-management Internet Services
http://proteger.at [gesprochen: Prot-e-schee]
Tel: 0660 / 415 65 31

****** Aktuelles Radiointerview! ******
http://www.it-podcast.at/aktuelle-sendung.html

// Wir haben im Moment zwei Häuser zu verkaufen:
// http://zmi.at/langegg/
// http://zmi.at/haus2009/