November 3, 2003 at 10:22 am
Not to long ago, I worked on an Oracle system in which there was three schemas, "ACTIVE", "WIP" (Work in Progress) and "HISTORY". Each schema was exactly the same. The active schema contained the data that was in force. When a user wanted to modify a record, all of the data in the active schema was copied to the WIP schema (by a SP) for modification. When the user was finished, a stored procedure was called that would copy the data from the ACTIVE schema to the HISTORY schema, and copy the data from the WIP schema to the ACTIVE schema.
It was very interesting. The application itself could not update the ACTIVE schema directly. All of the interaction was done in the WIP schema.
Has anyone implimented something similar in SQL Server? This was much easier to maintain in oracle because they have a "record" data construct which made writing a stored procedure to copy from schema to schema very easy. (its unfortunate that sql server doesn't!)
I would be interested to hear if someone set up their system this way or if they had any other suggestions.
Regards,
Joshua
November 3, 2003 at 6:17 pm
I've never seen anything copy the record to a WIP type buffer, typically it's auditing type code that tracks changes via triggers. I've got one system running that inserts new rows each time a change is made so that the history is actually in the table. Not ideal for high transactions, but kinda nice in this one instance.
Andy
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply