6.7 Undoing Table Changes6.7.1 ProblemYou want to implement an undo mechanism for a table that allows you to undo the most recent action. You can use a row-level audit log as the basis for an undo mechanism. 6.7.2 SolutionThe following stored procedure retrieves the most recent log record, which is the one with the highest LogID value, and reverses the action that caused that log record to be generated in the first place. CREATE PROCEDURE UndoLog AS DECLARE @ProductID CHAR(40) DECLARE @Qty INTEGER DECLARE @Type CHAR(1) SELECT @ProductID=ProductID, @Qty=Qty, @Type=Type FROM StockLog WHERE LogId=(SELECT MAX(LogId) FROM StockLog) AND Type!='N' IF @type='D' INSERT INTO Stock(ProductID,Qty) VALUES(@productID,@qty) IF @type='I' DELETE FROM Stock WHERE ProductID=@productID IF @type='E' UPDATE Stock SET Qty=@qty WHERE ProductID=@productID Following is a snapshot of the log after the first three inserts : ProductID Qty ---------- ---- Banana 10 Apple 20 Orange 30 6.7.3 DiscussionThe first thing this procedure does is to identify the most recent event recorded in the StockLog table. This is done by the SELECT query, which identifies the most recently generated log record, and then retrieves the stock data from that record. In the case of an update event, the type E record will be retrieved. Type N records are specifically excluded. The type E record can be used to undo the effects of an UPDATE statement, because it represents the before image of the stock record that was changed. With the data retrieved, it is fairly simple to restore the original state of the row in question: execute a statement opposite to the one that was initially executed. For an INSERT statement , the opposite action is to execute a DELETE statement and vice versa. For an UPDATE statement, the opposite action is to update the row involved and set the columns back to their original values. To do this, use the before image contained in the type E log entry for an UPDATE statement. The undo action resembles the use of the ROLLBACK statement to abort a transaction. The important differences, however, are that the action of the UndoLog procedure is, itself, logged in the audit log and that the action of the UndoLog procedure is more controllable. The undo procedure in this recipe only reverses the most recent event, but you have the ability to customize it to do more than that. |