Advertisements

Archive

Posts Tagged ‘Trigger’

Logging by Triggers in SQL Server

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
Advertisements
Categories: SQL, SQL Server, T-SQL Tags: , ,
%d bloggers like this: