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.