6.3 Row-Level Logging

6.3.1 Problem

You must implement a row-level log for a database table. With respect to our example, you wish to log changes to data in the Stock table so that you can review such changes at a later date. You need an auditing system that will allow you to track all the changes on each product.

6.3.2 Solution

As a basis for implementing this row-level log, you'll create a log table that mirrors the structure of your Stock table. The name of this table will be StockLog. All of the columns that make up the Stock table will be replicated in the StockLog table and will make up the operational portion of the log record. In addition, the StockLog table will contain some additional columns to indicate the time and type of operation that occurred. These additional columns will make up the log record's descriptor. Triggers will then be used to automatically log activity occurring on the Stock table.

The following CREATE TABLE statement creates the StockLog table:

CREATE TABLE StockLog(
   ProductID CHAR(40),
   Qty INTEGER,
   Type CHAR(1) CHECK (type IN ('D','E','I','N')), 
   UserId VARCHAR(40),
   EventTime DATETIME,
   LogId INTEGER IDENTITY,
   EventId UNIQUEIDENTIFIER
   PRIMARY KEY(LogId)
)

Each record contains a body and a descriptor. The body is usually a copy of the original record structure, and the descriptor holds additional information needed to identify specific events. Figure 6-4 highlights the structure of the StockLog record.

Figure 6-4. The StockLog Record

Once the StockLog table has been created, you can create the triggers that will record the activity on the Stock table. Three triggers are needed: one to record inserts in the log, one to record updates, and one to record deletes. You can use the following CREATE TRIGGER statements to create these triggers:

CREATE TRIGGER InsertStock
ON Stock
FOR INSERT
AS
BEGIN
   INSERT INTO StockLog 
      (ProductId, Qty, Type, EventID, UserId, EventTime)
      SELECT i.ProductId, i.Qty, 'I', NEWID(  ),
         CURRENT_USER, CURRENT_TIMESTAMP 
      FROM inserted I
END
GO

CREATE TRIGGER DeleteStock
ON Stock
FOR DELETE
AS 
BEGIN
   INSERT INTO StockLog
      (ProductId, Qty, Type, EventID, UserId, EventTime)
      SELECT d.ProductId, d.Qty, 'D',NEWID(  ), 
         CURRENT_USER, CURRENT_TIMESTAMP 
      FROM deleted d
END
GO

CREATE TRIGGER UpdateStock
ON Stock
FOR UPDATE
AS 
BEGIN
   
   DECLARE @ProductId CHAR(40)
   DECLARE @dQty INTEGER
   DECLARE @iQty INTEGER
   DECLARE @guid UNIQUEIDENTIFIER

   DECLARE UpdateStockLog CURSOR
      FOR SELECT d.ProductID,d.Qty,i.Qty 
             FROM deleted d, inserted i 
             WHERE d.ProductId=i.ProductId
   OPEN UpdateStockLog
   
   FETCH NEXT FROM UpdateStockLog
   INTO @ProductId, @dQty, @iQty

   WHILE (@@FETCH_STATUS=0) BEGIN
      SELECT @guid=NEWID(  )
      INSERT INTO StockLog
         (ProductID, Qty, Type, EventID, UserID, EventTime)
         VALUES(@productID,@dQty,'E', @guid,
            CURRENT_USER,CURRENT_TIMESTAMP)
       INSERT INTO StockLog
         (ProductID, Qty, Type, EventID, UserID, EventTime)
         VALUES(@productID,@iQty,'N', @guid,
            CURRENT_USER,CURRENT_TIMESTAMP)

      FETCH NEXT FROM UpdateStockLog
      INTO @ProductId, @dQty, @iQty
   END
  
   CLOSE UpdateStockLog
   DEALLOCATE UpdateStockLog
END
GO 

6.3.3 Discussion

This solution records activity in the Stock table by inserting records into the StockLog table. The idea is to include all the columns from the Stock table in the log, together with some additional columns to identify the user, the type of action taken, and the time the action was taken.

Some servers support mechanisms at the system level that are similar to the one shown here. These are sometimes referred to as journaling mechanisms. If these are available in your environment, you may want to use those native mechanisms instead of implementing an audit log using triggers. Either way, this example is useful because it will help you to understand the logic behind such mechanisms and the problems that might occur when using them.

6.3.3.1 Log table structure

The StockLog table contains five columns not in the original Stock table. These columns have the following purposes:

Type

Records the type of operation. The type code will be one of the following:

D

Marks a record that was deleted from the Stock table.

I

Marks a new record that was inserted into the Stock table.

E

Marks the before version of a Stock table record that was updated.

N

Marks the after version of a Stock table record that was updated.

UserID

Records the name of the user who performed the operation that was logged.

EventTime

Records the time at which an operation occurred.

LogId

Serves to identify each log record uniquely. Since this ID number is sequential, it also serves as a basis for correctly ordering the log records.

EventId

Stores event identification. This is primarily used with update actions where two log records are used to represent one event. In such a case, both log entries will be given the same value for this column. Linking the before and after versions of an updated record facilitates future analysis of the log.

In the triggers created for this recipe, the CURRENT_TIMESTAMP and CURRENT_USERfunctions are used to populate the EventTime and UserId columns. Not all database servers use the same names for these functions, but all database servers implement this type of functionality. If you are using a database other than SQL Server, you may need to make minor changes to the example code for it to run in your environment.

The LogId column is incremented sequentially by virtue of its being declared using the IDENTITY keyword when the StockLog table was created. Again, different databases have different mechanisms for implementing sequentially increasing primary keys. Oracle, for example, requires that you use a stored sequence. Use a mechanism appropriate for your environment.

6.3.3.2 Log triggers

The three triggers used to maintain the log correspond to the three SQL statements — INSERT, DELETE, and UPDATE — that can be used to change the data in a table. For INSERT and DELETE statements, the triggers simply need to make a copy of the row being inserted or deleted and log it together with the user ID, timestamp, operation code, and log sequence number. For example, if you inserted two records and then deleted one, your log might look as follows:

ProductID  Qty  Type UserId  EventTime                LogId  
---------- ---- ---- ------- ------------------------ ------ 
Bananas    100  I    Jenny   2000-06-27 22:41:41.150  1      
Apples     40   I    Teena   2000-06-27 22:41:41.177  2      
Apples     40   D    Teena   2000-06-27 22:41:41.183  3

The update trigger is a bit more complex than the other two, because it needs to deal with two versions of the row being updated — the original version and the modified version. One way to think of an update is as a combination of a delete followed by an insert. The deleted row represents the original version of the row, while the inserted row represents the new version. Our update trigger records the execution of an UPDATE statement in terms of those two steps. The deletion of the original row is marked using the type code E. That is immediately followed by an insert marked using the type code N. The codes E and N are used to differentiate update events from real delete and insert events. The following example shows the log records that would be generated as a result of a change in the quantity of bananas in the warehouse:

ProductID  Qty  Type UserId  EventTime                LogId  
---------- ---- ---- ------- ------------------------ ------ 
Bananas    100  E    Jenny   2000-06-27 22:44:13.060  4      
Bananas    200  N    Jenny   2000-06-27 22:44:13.060  5

With updates, two log entries are generated for one event. Special attention needs to be given to be sure that you can come back later and link up the two rows in question for any given update operation. In this recipe, we use the EventId column to record a unique identifier for each event. The EventId column was declared with the UNIQUEIDENTIFIER type — a special type reserved by SQL Server for system-generated unique identifiers. Each log trigger makes a call to the NEWID( ) function to retrieve a unique identifier for each event. This identifier is stored in the EventId column in all log records generated as a result of the event.

While values in columns of type UNIQUEIDENTIFIER are guaranteed unique, they are not guaranteed to be sequential. To ensure the sequential nature of the log, an IDENTITY column (LogID in this case) must be used.

The update trigger joins are inserted and deleted on their respective ProductId values. The deleted table holds the before versions of all rows being updated, while the inserted table holds the after versions of those same rows. The join on ProductId serves to match each before row with its corresponding after row. This approach, while efficient, requires that updates to a row's primary key values (ProductId in this case) never occur.

In SQL Server, triggers are executed just once in response to a triggering statement. SQL, however, allows you to modify many rows with one statement. To generate a unique event ID for each row being updated by an UPDATE statement, the UpdateStock trigger uses a cursor to iterate through each of the affected rows. A new event ID is generated each time through the loop, and this event ID is recorded in the log entries for the row being processed.

With a log such as the StockLog table, it is possible to play back events in the order in which they occur. The table is essentially a journal of events created sequentially and classified by type. Thus, it is possible to restore the table to the way it was at any point in time, and it is also possible to track the activity of each individual user.

While the solution shown in this recipe allows you to track, insert, update, and delete an activity, it does not allow you to track query an activity — triggers are not available on SELECT statements. If you need to record queries in an audit log, you can restrict table access to a stored procedure, force all database users to query through that procedure, and then have the stored procedure record those queries in a log.