When working with SQL Server, it’s important to have a way to track table activity. One of the easiest ways to achieve this is by adding a series of audit columns to the table. These columns provide valuable information such as when a row was created, who created it, when it was last updated, and who last updated it.

Typically, a set of four columns is used for auditing purposes. However, you can customize this based on your specific requirements. The four common columns are:

• CreateUser: Stores the username of the user who created the row.
• CreateDate: Stores the date and time when the row was created.
• LastUpdateUser: Stores the username of the user who last updated the row.
• LastUpdateDate: Stores the date and time of the last update.

By keeping the column names consistent across tables, it becomes easier to find and analyze the audit information. Additionally, writing dynamic code to review the information in bulk becomes more straightforward.

Let’s take a look at an example of how to create these audit columns:

CREATE TABLE AuditColumns (
  Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  Col1 varchar(50),
  CreateUser varchar(50) CONSTRAINT df_CreateUser DEFAULT original_login(),
  CreateDate datetime CONSTRAINT df_CreateDate DEFAULT getdate(),
  LastUpdateUser varchar(50) CONSTRAINT df_LastUpdateUser DEFAULT original_login(),
  LastUpdateDate datetime CONSTRAINT df_LastUpdateDate DEFAULT getdate()
);

In this example, the user columns are defined as varchar(50), which is usually sufficient. However, you can adjust the size based on your needs. The original_login() function is used to retrieve the username of the user who performed the action. If necessary, you can use a different function depending on how your application connects to the database.

The date columns are defined as datetime, but you can explore other options if needed. It’s important to consider the data types you use, as they can impact the storage size. In this case, the chosen data types add an additional 116 bytes per row.

To capture update information, we need to create a trigger:

CREATE TRIGGER upd_AuditColumns
ON dbo.AuditColumns AFTER UPDATE
AS
BEGIN
  IF UPDATE(LastUpdateUser) AND UPDATE(LastUpdateDate)
    RETURN
 
  UPDATE AuditColumns
  SET LastUpdateUser = DEFAULT,
      LastUpdateDate = DEFAULT
  FROM AuditColumns
  JOIN Inserted
  ON AuditColumns.Id = Inserted.Id
END;

This trigger is designed to handle updates to the last update columns. The IF UPDATE() statement allows us to skip the trigger if someone manually updates these columns. By using the DEFAULT keyword in the update statement, we refer back to the default values defined for the columns.

It’s worth noting that defaults won’t work if you explicitly include the column in your command. However, in this case, we’ve chosen not to handle that scenario. The trigger is primarily intended for application inserts/updates, while still allowing manual changes without extra work.

When joining the inserted system view to the original table using the primary key column(s), the trigger can handle both single row and multi-row updates. It’s essential to ensure that your triggers can handle multi-row operations.

As with any changes to your database schema or code, it’s crucial to thoroughly test your application to avoid introducing any bugs.

By implementing audit columns and triggers, you can easily track table activity and gain insights into who created or updated rows, as well as when these actions occurred. This information can be invaluable for auditing purposes and troubleshooting.