Creating an auditing strategy when a user has been defined in a table instead of in SQL

Question

To preface this post, I'm pretty new to database design, so please excuse me if there's already a simple or immediate answer to this post.

In my DB I have a table called t_employees which acts as a "user" table. In other words, tables that keep track of things like "modifiedBy", "createdBy", etc. use this table.

The table looks like this:

CREATE TABLE t_employees (
    employeeId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    employeeExternalId VARCHAR(50) NOT NULL,
    email NVARCHAR(128) NOT NULL,
    passwordHash NVARCHAR(128),
    firstName NVARCHAR(60),
    lastName NVARCHAR(60),
    dateHired DATE,
    gender INT,
    DOB DATE,
    ...
    --And a bunch of other random fields that an employee can use
);

And this is where my question resides.

I'm trying to come up with an auditing solution that will capture "who modified this data" when an update, insert, or delete statement occurs in my application. I've looked into threads like this where people can audit a table via triggers that keep track of the system user who's making the changes. However, this approach doesn't work for me as I'm trying to keep track of the employeeId from the t_employees table who made the changes and I only have 1 system user in my database.

I've thought of creating an SP that I call before every update, insert, or delete statement that logs what data is being inserted, updated, or deleted along with the employeeId of the user who made the request into an audit table. However, I feel like there must be a better solution to this as this seems kind of redundant.

Any help or advice would be very much appreciated. Thank you.

Answer 1

There are a number of approaches you can take to handle auditing changes, but here are some of the more common ones:

1. The application layer is responsible for inserting a new record into an audit table for each action taken in the system that you wish to track. This is the only way you will be able to capture employeeId from your example, as the database engine has no knowledge of them as they are not the user performing the DML operation.

2. Create a trigger for insert/update/delete on the tables you want to audit and insert a new record into an audit table with the fields you need. Keep in mind that triggers can have an impact on performance

3. Add a RevisionNumber and IsDeleted flag to your table. The gist is you always insert a new record into the table when a change occurs and set the IsDeleted flag to 1 when the application logically deletes the row. In order to get the "active" view of the table, you need to use the row_number() function to partition by the unique key and order by the row number descending and take the top 1 in the window function. This is much more involved, but it's an elegant solution to avoid multiple tables and provide the entire history of changes to all columns inline.

4. Use SQL Server Change Tracking to determine that a row was inserted, updated, or deleted from tables flagged for change tracking. You can opt to track which columns were changed in the DML, but you cannot track what values actually changed. It's great for knowing that something changed, but it can't tell you what the data changed from.

5. Use SQL Server Change Data Capture to determine that a row was inserted, updated, or deleted. Unlike change tracking, CDC can tell you what the value was pre- and post- DML operation since it reads the changes from the transaction log instead of a system table.

Options 3 through 5 are typically more involved and CDC requires Enterprise Edition. The simplest solutions are 1 and 2, but they tend to get out of hand as the number of tables you wish to audit increases. In the end it's up to you to decide what's best for your application and environment.

Answer 2

Adding to what @DForck42 said- username is best left to the application layer. But since these tables don't have too many changes everyday,I would include a

     IsDeleted (bit)

column and implement a soft delete (1 or 0).