Big Data, Data Analytics and Data Science | BI-BIGDATA.com

We can alternatively use SQL Server feature CDC (Change Data Capture), but here I am providing one more way of doing it (custom solution).

Db Objects: (two tables and two triggers)

1. TargetTableName: Table whose data we want to log
2. LogTableName: Table where data will be logged. This table structure will be same as TargetTableName, and additionally this table will have three extra columns namely
   • [log_action_type]
   • [log_action_date]
   • [log_action_user]
   • [log_action_hostname]
3. tl_TargetTableName: Trigger to log DML changes (Update and Delete only)
4. tu_LogTableName: Trigger to avoid Delete or Update operations operations on logging table

 
use dbname
go

alter table LogTableName add [log_action_type] 	[char](1) not null;
alter table LogTableName add [log_action_date] 	[datetime] not null default getdate();
alter table LogTableName add [log_action_user] 	[varchar](50) null;
alter table LogTableName add [log_action_hostname] 	[varchar](50) default host_name();

go

IF OBJECT_ID ('dbo.tu_LogTableName ','TR') IS NOT NULL
   DROP TRIGGER dbo.tu_LogTableName;
GO
-- =============================================
-- Author:		Umesh Patil	
-- Create date: 2014/08/26
-- Description:	Restirct Delete or Update operations operations on logging table
-- =============================================
CREATE TRIGGER dbo.tu_LogTableName  
   ON  dbo.LogTableName 
   FOR DELETE,UPDATE
AS 
BEGIN
	RAISERROR ('Can not perform Delete or Update operations on UserTableName table, either disable/delete the trigger',16,1);
	ROLLBACK TRANSACTION;
	RETURN;
END
GO

use dbname
go

IF OBJECT_ID ('dbo.tl_TargetTableName','TR') IS NOT NULL
   DROP TRIGGER dbo.tl_TargetTableName;
GO
-- =============================================
-- Author:		Umesh Patil	
-- Create date: 2014/08/26
-- Description:	Log DML changes (Update and Delete only)
-- =============================================
CREATE TRIGGER dbo.tl_TargetTableName 
   ON  dbo.TargetTableName
   FOR DELETE,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	DECLARE @ActionType char(1)

	IF EXISTS (SELECT 1 FROM deleted)
		BEGIN
			IF EXISTS(SELECT 1 FROM inserted)
				SET @ActionType='U'
			ELSE
				SET @ActionType='D'
		END

	IF  @ActionType='U' OR  @ActionType='D'
		INSERT INTO [dbo].[TargetTableName]
           	([PrimaryKey_Column]
		  --include all columns
		,[log_action_type]
		,[log_action_date]
		,[log_action_user]
		  ,[log_action_hostname])
		select		
			[PrimaryKey_Column]
			--include all columns
			,@ActionType as [log_action_type]
			,getdate() as [log_action_date]
			,suser_sname() as [log_action_user]
			,host_name() as [log_action_hostname]
		from deleted
END
GO