Normalized Table Structure for Logging

How can I normalize a table that has four columns for logging the latest activity for a single row:

-
Created On

-
Created By

-
Modified On

-
Modified By

We have dozens of tables, all with these 4 columns. Is there a way or pattern to normalize this in an efficient and flexible manner?

I would suggest not logging the "latest activity" but rather keeping a full audit trail. In order to minimize space requirements, you might want three tables:

CREATE TABLE dbo.Users
(
  UserID TINYINT IDENTITY(1,1) PRIMARY KEY, -- assuming <= 255 users
  Username NVARCHAR(128) NOT NULL UNIQUE,
  /* , other columns */
);

CREATE TABLE dbo.Tables
(
  TableID TINYINT IDENTITY(1,1) PRIMARY KEY, -- assuming <= 255 tables
  Name NVARCHAR(128) NOT NULL UNIQUE
  /* , other columns */
);

This auditing table assumes that all of the audited tables have an INT primary key (or a PK that will fit into INT). This will obviously be more complex if you have different data types, or compound primary keys, in which case you may just consider different auditing tables - still more valuable, IMHO, than only keeping the last modification for any one row.

CREATE TABLE dbo.AuditLog
(
  TableID       TINYINT NOT NULL 
                FOREIGN KEY REFERENCES dbo.Tables(TableID),
  ID            INT, -- loose reference to entity table's PK
  Action        CHAR(1) CHECK (Action IN ('I', 'U', 'D')),
  UserID        TINYINT NULL -- just in case
                FOREIGN KEY REFERENCES dbo.Users(UserID),
  EventDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

(Note that I intentionally did not dictate what the clustered index should be, since I don't know all of your query patterns aside from the one mentioned in the question. It is quite likely that you will want to cluster leading on EventDateTime, especially if you are going to query for events that happened recently, and/or you are going to purge data periodically, which is never a bad idea. This will at least ensure that new rows are added to the "end" of the table instead of page splits happening all over the place if you choose something like TableID as the leading column.)

Now your trigger can check the user name (not sure about your authentication method, but this may be via SUSER_SNAME()), it knows what table it's dealing with, it can determine the action, so it just has to add the ID(s) from inserted/deleted. Hokey example that assumes a table named dbo.foo with a primary key called FooID:

INSERT dbo.tables(Name) SELECT N'dbo.foo';
GO

CREATE TRIGGER dbo.AuditFoo
ON dbo.foo
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @UserID TINYINT, @TableID TINYINT, @now DATETIME;

  SELECT @UserID = UserID -- could be NULL
    FROM dbo.Users WHERE Username = SUSER_SNAME();

  SELECT @TableID = TableID 
    FROM dbo.Tables WHERE Name = N'dbo.foo';

  -- inserts
  INSERT dbo.AuditLog(TableID, ID, Action, UserID)
  SELECT @TableID, FooID, 'I', @UserID
    FROM inserted AS i WHERE NOT EXISTS
    (SELECT 1 FROM deleted AS d WHERE d.FooID = i.FooID);

  -- updates
  INSERT dbo.AuditLog(TableID, ID, Action, UserID)
  SELECT @TableID, FooID, 'U', @UserID
    FROM inserted AS i WHERE EXISTS
    (SELECT 1 FROM deleted AS d WHERE d.FooID = i.FooID);

  -- deletes
  INSERT dbo.AuditLog(TableID, ID, Action, UserID)
  SELECT @TableID, FooID, 'D', @UserID
    FROM deleted AS d WHERE NOT EXISTS
    (SELECT 1 FROM inserted AS i WHERE i.FooID = d.FooID);
END
GO

Only one of those inserts will fire per statement / trigger invocation (well, to be pedantic, the trigger itself will fire multiple times in the event of MERGE).

If you don't want to keep a list of users / tables you can always populate this dynamically (though if you're not strictly controlling the list you may seriously reconsider the TINYINT suggestion above). E.g. to capture users you're seeing for the first time and haven't manually inventoried:

SELECT @UserID = UserID FROM dbo.Users WHERE Username = SUSER_SNAME();

  IF @UserID IS NULL
  BEGIN
    INSERT dbo.Users(Username) SELECT SUSER_SNAME();
    SELECT @UserID = SCOPE_IDENTITY();
  END

But for the table this doesn't make much sense. Just add the row when you create the trigger, and you can even hard-code the TableID instead of deriving it at runtime.

Once you are collecting data in dbo.AuditLog, you should be able to easily derive information about the last action of any specific type, restricted to a table, user, or even individual entity. If you need help constructing any of those queries, please start a new question with the schema, some sample rows and desired / expected results. Using SQLFiddle can be quite helpful.

StackExchange Database Administrators Q#30911, answer score: 6