6.7 Undoing Table Changes

6.7.1 Problem

You 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 Solution

The 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 Discussion

The 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.