Sql-server – How to fetch transaction details for Logs Table in SQL Server

sql servertransaction-log

I want to create a tracking table for changes made to the database.

A tracking table called Logs must be created into which all changes (insert and delete) that are made in the database will be entered on the following tables: Author, Publisher, Chapter

The fields of the Logs table will be:

DateOfLog (DateTime)
ActionType (varchar (15))
LogTable (varchar (15))
ActionData (nvarchar (max))

I have created the Logs Table as well as writing a trigger to load information in Logs but the problem is with LogTable and ActionData fields.

Please help me to get the name of table on which action performed and the data in string that was affected.

Best Answer

You might be better off using something like Change Data Capture to implement this, rather than a custom solution. See About Change Data Capture (SQL Server) on MS Docs:

Change data capture records insert, update, and delete activity that is applied to a SQL Server table. This makes the details of the changes available in an easily consumed relational format.

Another option would be to alter the tables you'd like to keep history on to be system-versioned. The technology for this is called "Temporal Tables" and you can read more about it on MS Docs at Temporal Tables:

A system-versioned temporal table is a type of user table designed to keep a full history of data changes and allow easy point in time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (i.e. database engine).

If you are determined to roll your own auditing, I've seen the approach outlined in this Stack Overflow Q&A used successfully in production: Log record changes in SQL server in an audit table