On Mon, 2006-02-13 at 09:50 -0500, Chris Hoover wrote:
> The problem I am having is how can you safely move records between
> main_table_live and one of the other partition tables? Obviously,
> this will have to happen as our users work the data and the records
> enter into on of the 2 closed statuses ('C' or 'D'). When the status
> is changed to C or D, I need to first move the record and run an
> update against the moved record to make sure all fields are updated
> and our auditing triggers are fired.
My not just delete from the live table and insert into the partitioned
history table? That can be done atomically and with triggers.
Alternatively, keep all versions of the record as it develops over time
so the key becomes PK+timestamp and all writes are inserts. That way you
don't need an audit table, since you never change data, so probably
overall a smaller system and one less prone to bugs in the data update
processes.
Best Regards, Simon Riggs