Audit Management - Used common table.

There are varies methods available for audit management. I would like to shared here one of method I followed in my recent project.

Approach is create a common table to records audit data in a Jason format. Implementation is  very simple and easy. bellow method will support bulk insert/update as well. 

Followed following steps:

01. Create common table

03. Create SP helper to generate trigger passing table Name and schema.  

01. Table Structure  :
Uid |TableName | OldValues | NewValues | CreatedDate |HostName |DbLogin

02. Create Helper SP to generate audit trigger,

ALTER PROCEDURE [dbo].[AuditTrigerHelper]
@SCHEMA as Nvarchar(128),
@TABLE_NAME as nvarchar(128)
AS
BEGIN
   DECLARE @sql varchar(8000);

    SELECT @sql = 'CREATE TRIGGER '+@SCHEMA +'.[trg_Audit_' + @TABLE_NAME + '] ON  ' + @SCHEMA + '.' + @TABLE_NAME + '
   AFTER UPDATE,INSERT,DELETE
AS
Declare
@TableName as nvarchar(128) ='''+ @SCHEMA +'.' + @TABLE_NAME +''',
@KeyValue as Nvarchar(128),
@OldValue as Nvarchar(max),
@NewValue as Nvarchar(max);
BEGIN
--get only delete records
IF EXISTS(SELECT 1 FROM deleted A
LEFT JOIN inserted B on A.Uid = B.Uid
where B.Uid IS NULL)
BEGIN
INSERT INTO [Audit].[Audit](
TableName
,KeyValue
,OldValues
,NewValues
,CreatedDate
,HostName
,DbLogin)
SELECT
   @TableName,
    A.Uid,
AA.JSON_Data as OldValue
,null as newValues
,GETDATE(),
    HOST_NAME(),
    SYSTEM_USER
from deleted A
Cross APPLY (select JSON_Data = (
select AB.* from deleted AB
where A.Uid = AB.Uid for JSON Path)) AA
END

--check insert and update records
;with CTE_inserted AS (
SELECT A.Uid, AA.JSON_Data as NewValue
from inserted A
Cross APPLY (select JSON_Data = (
select AB.* from inserted AB
where A.Uid = AB.Uid
for JSON Path)) AA
)
,CTE_deleted AS (
SELECT A.Uid, AA.JSON_Data as OldValue
from deleted A
Cross APPLY (select JSON_Data = (
select AB.* from deleted AB
where A.Uid = AB.Uid
for JSON Path)) AA
)

INSERT INTO [Audit].[Audit](
TableName
,KeyValue
,OldValues
,NewValues
,CreatedDate
,HostName
,DbLogin)
SELECT
   @TableName,
   c.Uid,
   D.OldValue,
   C.NewValue,  
   GETDATE(),
   HOST_NAME(),
   SYSTEM_USER
from CTE_inserted C
LEFT JOIN  CTE_deleted D on C.uid = D.UID
END'
--select @sql
EXEC(@sql)
END

04.  Used Audit TriggerHelper to generate sql table trigger passing the table Name and schema
-- ex:   Exec EXEC AuditTrigerGenerationHelper 'schemaName' ,'TableName'

05. Trigger will be generated as below,

Create TRIGGER [schemaName].[trg_Audit_TableName] ON  [schemaName].[TableName]
   AFTER UPDATE,INSERT,DELETE
AS
Declare
@TableName as nvarchar(128) =schemaName.TableName',
@KeyValue as Nvarchar(128),
@OldValue as Nvarchar(max),
@NewValue as Nvarchar(max);
BEGIN
--get only delete records
IF EXISTS(SELECT 1 FROM deleted A
LEFT JOIN inserted B on A.Uid = B.Uid
where B.Uid IS NULL)
BEGIN
INSERT INTO [Audit].[Audit](
TableName
,KeyValue
,OldValues
,NewValues
,CreatedDate
,HostName
,DbLogin)
SELECT
   @TableName,
    A.Uid,
AA.JSON_Data as OldValue
,null as newValues
,GETDATE(),
    HOST_NAME(),
    SYSTEM_USER
from deleted A
Cross APPLY (select JSON_Data = (
select AB.* from deleted AB
where A.Uid = AB.Uid for JSON Path)) AA
END

--check insert and update records
;with CTE_inserted AS (
SELECT A.Uid, AA.JSON_Data as NewValue
from inserted A
Cross APPLY (select JSON_Data = (
select AB.* from inserted AB
where A.Uid = AB.Uid
for JSON Path)) AA
)
,CTE_deleted AS (
SELECT A.Uid, AA.JSON_Data as OldValue
from deleted A
Cross APPLY (select JSON_Data = (
select AB.* from deleted AB
where A.Uid = AB.Uid
for JSON Path)) AA
)

INSERT INTO [Audit].[Audit](
TableName
,KeyValue
,OldValues
,NewValues
,CreatedDate
,HostName
,DbLogin)
SELECT
   @TableName,
   c.Uid,
   D.OldValue,
   C.NewValue,  
   GETDATE(),
   HOST_NAME(),
   SYSTEM_USER
from CTE_inserted C
LEFT JOIN  CTE_deleted D on C.uid = D.UID

END