How To Manage Insert And Update Audit Log In SQL Using Trigger

Introduction

In this article, we will learn how to manage insert and update audit log in SQL using trigger.

Audit Log

This feature is used to auditing capabilities like who made a change and when, but doesn’t offer other information, which today would be considered essential, like what was actually changes and will be manage all information in audit table.

I will be manage audit log on GABICCode table.

select * from GABICCode

If any change on columns like CCodeRange, CCodeName and CCodeDescripton so would manage audit log in audit table using trigger.

Create Audit Table

Create Audit table in SQL database. So It will manage audit log in it.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Audit](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [TypeRequest] [nvarchar](50) NULL,
  [Type] [nvarchar](max) NULL,
  [TableName] [nvarchar](max) NULL,
  [TypeData] [nvarchar](max) NULL,
  [Field] [nvarchar](max) NULL,
  [BeforeData] [nvarchar](max) NULL,
  [AfterData] [nvarchar](max) NULL,
  [UserRequest] [int] NULL,
  [CreatedDate] [datetime] NULL,
 CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Create Trigger For Insert GABICCode Audit Log

CREATE TRIGGER [dbo].[AuditTriggerInsertGABICCode]                  
ON GABICCode    
after INSERT                  
AS                  
    INSERT INTO [Audit]                  
                ([TypeRequest],                  
                 [Type],                  
                 [tablename],                  
     [typedata],                  
                 [field],                  
                 [beforedata],                  
                 [afterdata],                  
                 [userrequest],                  
                 [createddate]    
     )                  
    SELECT [TypeRequest] = 'Insert',                  
           [Type] = 'SIC_Codes',                  
           [TableName] = 'GABICCode',                  
           [TypeData] = NULL,                  
           [Field] = FieldValues.fieldname,                 
           [BeforeData] = NULL,                  
           [AfterData] = FieldValues.aftervalue ,        
           [UserRequest] = FieldValues.userrequest,                  
           [CreatedDate] = Sysdatetime()                  
                     
    FROM   (SELECT [Id_After] = inserted.[Id],    
       [CCodeRange_After] = inserted.[CCodeRange],                  
       [CCodeName_After] = inserted.[CCodeName],              
       [CCodeDescription_After] = inserted.[CCodeDescription],    
       [CreatedBy_After] = inserted.[Createdby],     
       [UpdatedBy_After] = inserted.[UpdatedBy]    
                 
  FROM   inserted                  
                   FULL OUTER JOIN deleted                  
                                ON inserted.[Id] = deleted.[Id]) AS                  
           RawData                  
           CROSS apply ( VALUES (N'CCodeRange',                  
                       Cast ([CCodeRange_after] AS NVARCHAR(max)),                  
                       Cast ([Id_after] AS NVARCHAR(max)),     
         Cast ([CreatedBy_After] AS NVARCHAR(max))),             
       (N'CCodeName',                  
        Cast ([CCodeName_after] AS NVARCHAR(max)),                  
        Cast ([Id_after] AS NVARCHAR(max)) ,      
        Cast ([CreatedBy_After] AS NVARCHAR(max))),             
         (N'CCodeDescription',                  
        Cast ([CCodeDescription_after] AS NVARCHAR(max)),                  
        Cast ([Id_after] AS NVARCHAR(max)),      
        Cast ([CreatedBy_After] AS NVARCHAR(max))),             
        (N'CreatedBy',                  
        Cast ([CreatedBy_after] AS NVARCHAR(max)),                  
        Cast ([Id_after] AS NVARCHAR(max)),    
        Cast ([CreatedBy_After] AS NVARCHAR(max))),             
        (N'UpdatedBy',                  
        Cast ([UpdatedBy_after] AS NVARCHAR(max)),                  
        Cast ([Id_after] AS NVARCHAR(max)),    
        Cast ([CreatedBy_After] AS NVARCHAR(max)))             
      ) AS                  
           FieldValues (                  
         fieldname, aftervalue, Id,userrequest)                  
    WHERE  ( aftervalue != ''                  
             AND aftervalue IS NOT NULL );

Create Trigger For Update GABICCode Audit Log

CREATE TRIGGER [dbo].[AuditTriggerUpdateGABICCode]                    
ON GABICCode                    
after UPDATE                    
AS                    
    INSERT INTO [dbo].[audit]                    
                ([typerequest],                    
                 [type],                    
                 [tablename],                    
                 [typedata],                    
                 [field],                    
                 [beforedata],                    
                 [afterdata],                    
                 [userrequest],                    
                 [createddate]            
     )                    
    SELECT [TypeRequest] = 'Update',                    
           [Type] = 'SIC_Codes',                    
           [TableName] = 'GABICCode',                    
           [TypeData] = NULL,                    
           [Field] = FieldValues.fieldname,                 
           [BeforeData] = FieldValues.beforevalue ,        
           [AfterData] = FieldValues.aftervalue ,        
           [UserRequest] = FieldValues.userrequest,                    
           [CreatedDate] = Sysdatetime()    
                    
    FROM   (SELECT [Id_After] = inserted.[Id],              
       [CCodeRange_Before] = deleted.[CCodeRange],                  
       [CCodeRange_After] = inserted.[CCodeRange],                     
       [CCodeName_Before] = deleted.[CCodeName],    
       [CCodeName_After] = inserted.[CCodeName],    
       [CCodeDescription_Before] = deleted.[CCodeDescription],    
       [CCodeDescription_After] = inserted.[CCodeDescription],    
       [CreatedBy_Before] = deleted.[CreatedBy],    
       [CreatedBy_After] = inserted.[CreatedBy],    
       [UpdatedBy_Before] = deleted.[UpdatedBy],    
       [UpdatedBy_After] = inserted.[UpdatedBy]    
                 
             FROM   inserted                    
                   FULL OUTER JOIN deleted                    
                                ON inserted.[Id] = deleted.[Id]) AS                    
           RawData                    
           CROSS apply ( VALUES (N'CCodeRange',                    
                       Cast ([CCodeRange_before] AS NVARCHAR(max)),                    
                       Cast ([CCodeRange_after] AS NVARCHAR(max)),                    
                       Cast ([Id_After] AS NVARCHAR(max)),    
        Cast ([UpdatedBy_After] AS NVARCHAR(max))),                       
      (N'CCodeName',                    
                       Cast ([CCodeName_before] AS NVARCHAR(max)),                    
                       Cast ([CCodeName_after] AS NVARCHAR(max)),                    
                       Cast ([Id_After] AS NVARCHAR(max)),    
        Cast ([UpdatedBy_After] AS NVARCHAR(max))),              
        (N'CCodeDescription',                    
                       Cast ([CCodeDescription_before] AS NVARCHAR(max)),                    
                       Cast ([CCodeDescription_after] AS NVARCHAR(max)),                    
                       Cast ([Id_After] AS NVARCHAR(max)) ,    
        Cast ([UpdatedBy_After] AS NVARCHAR(max))),              
        (N'CreatedBy',                    
                       Cast ([CreatedBy_before] AS NVARCHAR(max)),                    
                       Cast ([CreatedBy_after] AS NVARCHAR(max)),                    
                       Cast ([Id_After] AS NVARCHAR(max)) ,      
        Cast ([UpdatedBy_After] AS NVARCHAR(max))),              
        (N'UpdatedBy',                    
                       Cast ([UpdatedBy_before] AS NVARCHAR(max)),                    
                       Cast ([UpdatedBy_after] AS NVARCHAR(max)),                    
                       Cast ([Id_After] AS NVARCHAR(max)),    
        Cast ([UpdatedBy_After] AS NVARCHAR(max)))                    
       ) AS                    
                       FieldValues (                    
                  fieldname, beforevalue, aftervalue, idoffer,userrequest)                    
    WHERE  (beforevalue != aftervalue             
 and aftervalue != ''                    
             AND aftervalue iS not null)                    
 --          AND ;

If any changes on column so it will be add audit logs in audit table.

If you have any questions or face any problems about this article, please let me know in comments.

For new blogs check. here.

Thank You.