SQL Server Audit Log

SQL Server Audit Logging.sql

--Log Insert
CREATE TRIGGER LogInsert ON [dbo].[CustomerProduct]
FOR INSERT
AS
INSERT changelog SELECT TOP 1 getdate(), 'Insert', 'CustomerProduct', CustomerID, ProductID, 'PaymenttypeID', '', Paymenttypeid FROM inserted
INSERT changelog SELECT TOP 1 getdate(), 'Insert', 'CustomerProduct', CustomerID, ProductID, 'ExpirationDate', '', convert(varchar(12), ExpirationDate, 110) FROM inserted
INSERT changelog SELECT TOP 1 getdate(), 'Insert', 'CustomerProduct', CustomerID, ProductID, 'Rate', '', Rate FROM inserted

--Log Delete
CREATE TRIGGER LogDelete ON [dbo].[CustomerProduct]
FOR DELETE
AS
INSERT changelog SELECT getdate(), 'Delete', 'CustomerProduct', CustomerID, ProductID, 'PaymenttypeID', Paymenttypeid, '' FROM deleted
INSERT changelog SELECT getdate(), 'Delete', 'CustomerProduct', CustomerID, ProductID, 'ExpirationDate', convert(varchar(12), ExpirationDate, 110), '' FROM deleted
INSERT changelog SELECT getdate(), 'Delete', 'CustomerProduct', CustomerID, ProductID, 'Rate', Rate, '' FROM deleted

--Log Update
--Note: SET ANSI_NULLS OFF to correctly handle comparing of Null values for changes
CREATE TRIGGER LogUpdate ON [dbo].[CustomerProduct]
FOR UPDATE
AS
SET ANSI_NULLS OFF
IF ((SELECT PaymentTypeID FROM inserted) != (SELECT PaymentTypeID FROM deleted))
       INSERT changelog SELECT getdate(), 'Update', 'CustomerProduct', CustomerID, ProductID, 'PaymenttypeID', (SELECT PaymentTypeID FROM deleted), Paymenttypeid FROM inserted
IF ((SELECT ExpirationDate FROM inserted) != (SELECT ExpirationDate FROM deleted))
       INSERT changelog SELECT getdate(), 'Update', 'CustomerProduct', CustomerID, ProductID, 'ExpirationDate', (SELECT convert(varchar(12), ExpirationDate, 110) FROM deleted), convert(varchar(12), ExpirationDate, 110) FROM inserted
IF ((SELECT Rate FROM inserted) != (SELECT Rate FROM deleted))
       INSERT changelog SELECT getdate(), 'Update', 'CustomerProduct', CustomerID, ProductID, 'Rate', (SELECT Rate FROM deleted), Rate FROM inserted


--ChangeLog Table
CREATE TABLE [ChangeLog] (
       [LogID] [int] IDENTITY (1, 1) NOT NULL ,
       [ChangeDateTime] [smalldatetime] NOT NULL ,
       [ChangeAction] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
       [TableName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
       [TableID1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
       [TableID2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
       [ColumnName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
       [OldValue] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
       [NewValue] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
       CONSTRAINT [PK_ChangeLog] PRIMARY KEY  CLUSTERED
       (
               [LogID]
       )  ON [PRIMARY]
) ON [PRIMARY]
GO