Audit – Database Trigger – Part 1

Audit is crucial in applications in all industries, for both compliance and troubleshooting purposes. Trigger is one of the way of implementing audit by logging the information on the database level rather than application level which is the trend moving forward now.

There are multiple ways to implement audit using trigger, but today we will be talking about the method where old value and new value is logged which is a fairly standard implementation.

Typically a stored procedure is used to generate the DDL Trigger for each table to be logged. During the implementation of the stored procedure, you will need to map each column value into the audit table. While using a nvarchar field could work, but personally I’d prefer having the native datatype to be used in the insertion for less headache of something changed or behaving weird.

To generate the datatype of each of the columns, you can use the below scalar function to generate the datatype based on information from information_schema.columns.

The SQL below is built for SQL Server, so you will need some changes if you’re to implement it in a different RDBMS

Link: https://gist.github.com/wcypierre/e19f2f99a130a7d82b123a88d6fa97c3

More on the implementation will be discussed on Part 2 of this sharing.