DB Design, need to allow for archiving and rollbacks

[NeilTrain]

I am designing a rather large SQL 2k5 DB. I need to track changes on certain tables, and I also need the ability to "roll back" changes.

Heres and example of a table and its log/archive

UserRoles
---------
UserRoleID
UserID
RoleID

UserRolesArchive
----------------
UserRoleID
UserID
RoleID
dbAction [Insert,Update,Delete]
ActionDate

The archive table will be populated via triggers. I figure this would allow for an archive, so that we can see all values the record ever had, even if it doesnt exist anymore. And also allows us to "roll back" changes if a user messes something up or changes thier mind. The above is just a simple example, our main focus is the product pricing tables, which have lots of prices that change a lot.

Has anyone done a db like this and if so what are your thoughts? Is this an acceptable approach to this problem or is there something better/more standard?

Any and all comments are much appreciated.

 

[ESquared]

I think it's a reasonable way to do it. Don't try to save space by not inserting the "current" value to the Archive--that complicates way too many things. The archive should function separately from the data tables.

Don't forget to add a ActionUser column to the archive to record who was responsible for the insert, update, or delete.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[NeilTrain]

Ya I'm not afraid to waste space, at least in a table designed to be an archive.

I was rather trying to have a "universal rule" of which data to archive. But unfortunately if i choose to archive the "before" data, it would be empty after an insert, and if I tried to archive the "after" data, there would be none after a delete. So i decided to use after for inserts, and before for update and delete.

And you're right i need to add a ActionUser to each of the archive tables as well, meant to but I forgot.

I think I will also add "SavePoint" to the dbAction types, where Insert,Update,Delete are fired by the triggers, Saves will just be a total copy of all records into the Archive to allow for a system wide rollback to that save point.

 

[bborissov]

Remember that an UPDATE is actually a DELETE and after that INSERT, so in you could do:

CREATE TRIGGER blahblah OF ...
       FROM DELETE, INSERT, UPDATE
AS
INSERT INTO Archive
SELECT ...., 'DELETED' AS Status
       FROM Deleted

INSERT INTO Archive
SELECT ...., 'INSERTED' AS Status
       FROM Inserted

This is a very rough code (I even didn't check the syntax) but it is only to give you an idea

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[mrdenny]

But unfortunately if i choose to archive the "before" data, it would be empty after an insert, and if I tried to archive the "after" data, there would be none after a delete.

Not quite sure what you mean here.

If you have an after trigger which handles UPDATE and DELETE all the old records can be easily logged into your archive table.

CREATE TRIGGER t_UserRoles_Archive on UserRoles
FOR UPDATE, DELETE
AS
BEGIN
    INSERT INTO UserRolesArchive
    (UserID, RoleID, dbAction, ActionDate)
    SELECT deleted.UserID, deleted.RoleID, case when inserted.UserID IS NULL then 'Delete' else 'Update' end, getdate()
    FROM deleted
    left outer join inserted on deleted.UserID = inserted.UserID
END
GO

Inserts aren't logged into the table as the current insert is the first record, and any data changes to that record the old value, and when the record is deleted the value is logged and flagged as a delete.

As triggers can be a resource hog they should be kept as simple as possible.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ESquared]

I personally don't like not having the latest values in my archive table, requiring joins to the main table to find out that value.

So i decided to use after for inserts, and before for update and delete.

I would suggest after for updates. Otherwise you'll run into the what I mentioned above. So here's my version:

CREATE TRIGGER t_UserRoles_Archive on UserRoles
FOR UPDATE, DELETE
AS
DECLARE @Action char(1)
IF NOT EXISTS (SELECT * FROM Deleted) SET @Action = 'I' 
ELSE IF EXISTS (SELECT * FROM Inserted) SET @Action = 'U'
ELSE SET @Action = 'D'

IF @Action = 'D'
   INSERT UserRolesArchive (UserID, RoleID, dbAction, ActionDate)
   SELECT UserID, RoleID, @Action, GetDate()
   FROM Deleted D
ELSE
   INSERT UserRolesArchive (UserID, RoleID, dbAction, ActionDate)
   SELECT UserID, RoleID, @Action, GetDate()
   FROM Inserted I
GO

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[NeilTrain]

bborissov, you are right, I forgot that Updates put data in both of the special tables. Been a while since I wrote a trigger.

 

[ESquared]

You don't need the inserted values during an update because they are the previous row for that ID in the history table. You'd be inserting duplicate rows for no reason.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]