Create Log tables and Triggers

,

Usually in business applications it is necessary to maintain the history of the data being modified for auditing purposes or for later analysis. Proven way to achieve this is to create a log table for each datatable with an additional column "Action" to indicate what the user has done on the data (either insert, update or delete).
And a trigger will be created on the base data table to move the modified data to the log table

Below script helps the database developer to automatically create log tables and triggers on base tables automatically for all the database tables

-- 1. Create the table to store the names of tables for which log
--    tables need to be created

CREATE TABLE [dbo].[tbl_Data_Tables] (
	[Id] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Table_Name] [nvarchar] (100) NOT NULL ,
	[Log_Table_Name] [nvarchar] (104) NOT NULL ,
	CONSTRAINT [PK_tbl_Data_Tables] PRIMARY KEY  CLUSTERED 
	(
		[Id]
	) WITH  FILLFACTOR = 85  ON [PRIMARY] 
) ON [PRIMARY]
GO

-- 2. Exectute below script to create the common function to transfer data to log tables

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE     PROCEDURE dbo.proc_Insert_To_Log
	@Table_Name	sysname
AS
-- Insert data to log file
DECLARE @insRowCount Integer,
	@delRowCount Integer,
	@Operation Char(1),
	@Log_Table sysname,
	@strSQL0    Nvarchar(100),
	@strSourSQL1    varchar(8000),
	@strDestSQL1    varchar(8000),
	@Tbl_Col_Name	NVarchar(200),
	@IdentityRowCount	integer

	-- Get the log table name from 
	SELECT 	@Log_Table = Log_Table_Name
	FROM 	tbl_Data_Tables 
	WHERE 	Table_Name = @Table_Name 
	
	-- Get the inserted and deleted row count	
	SELECT 	@insRowCount = count(*) FROM #Tmp_Inserted
	SELECT 	@delRowCount = count(*) FROM #Tmp_Deleted

	-- If no row is inserted or deleted return
	IF @insRowCount = 0 AND @delRowCount = 0
		Return
	-- Get the activity	-- if data is present in both insert and delete temp tables
	-- then an update statement is fired
	If @insRowCount > 0 AND @delRowCount > 0 
		SET @Operation = 'U'
	ELSE IF @insRowCount > 0 AND @delRowCount = 0	-- insert statement is fired
		SET @Operation = 'I'
	ELSE IF @insRowCount = 0 AND @delRowCount > 0	-- delete statement is fired
		SET @Operation = 'D'

	-- Prepare the statement to insert data into logtable
	SET @strSourSQL1 = 'INSERT INTO ' + @Log_Table + '('

	-- Open cursor to get the columns of the current table from sysobjects
	SET @strDestSQL1 = ' SELECT '	
	DECLARE Column_Cursor CURSOR FOR
		SELECT 	T1.Name As [Column Name] FROM [SYSCOLUMNS] T1
		INNER 	JOIN [SYSOBJECTS] T2 ON T1.id = T2.id
		WHERE  	T2.name = @Table_Name

	OPEN Column_Cursor
	FETCH FROM Column_Cursor INTO @Tbl_Col_Name
		WHILE @@Fetch_Status = 0
		BEGIN
			SET @strSourSQL1 = @strSourSQL1  + '[' + @Tbl_Col_Name + ']'
			SET @strDestSQL1 = @strDestSQL1 + + '[' + @Tbl_Col_Name + ']'
	
			SET @strSourSQL1 = @strSourSQL1  + ','
			SET @strDestSQL1 = @strDestSQL1 + ','
	
			FETCH NEXT FROM Column_Cursor INTO @Tbl_Col_Name
		END
	CLOSE Column_Cursor
	
	DEALLOCATE Column_Cursor
	-- Append activity column
	SET @strSourSQL1 = @strSourSQL1  + '[Activity])'
	SET @strDestSQL1 = @strDestSQL1 + '''' + @Operation + ''''

	-- Get the data based on activity
	IF @Operation = 'I'
		SET @strDestSQL1 = @strDestSQL1 +  ' FROM #Tmp_Inserted'
	ELSE
	SET @strDestSQL1 = @strDestSQL1 +  ' FROM #Tmp_Deleted'

	-- Check whether the log table has an identity column
	SELECT 	@IdentityRowCount = COUNT(T2.Name) FROM SYSOBJECTS T1
	INNER 	JOIN SYSCOLUMNS  T2 ON T1.ID = T2.ID
	WHERE 	T1.name = @Log_Table
		AND T2.Status = 128

 	-- Enable identity insert if an identity column exists
	IF @IdentityRowCount > 0 
		SET  @strSQL0 = 'SET IDENTITY_INSERT ' +  @Log_Table + ' ON; '
	ELSE
		SET  @strSQL0 = ''
	
	-- Execute the statements
	EXEC( @strSQL0 + @strSourSQL1 + @strDestSQL1)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-- 3. Procedure to drop constraint on log tables
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE proc dbo.proc_Drop_Constraints
	@tablename	sysname
AS
	-- sp_drop_constraints will drop all constraints on the specified table, 
	-- including CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE, DEFAULT and  Identiy constraints. 

	SET NOCOUNT ON
	DECLARE @constname	sysname,
		@cmd		varchar(1024)
	
	DECLARE curs_constraints CURSOR FOR
		SELECT 	NAME
		FROM 	sysobjects 
		WHERE 	xtype in ('C', 'F', 'PK', 'UQ', 'D')
		AND	(status & 64) = 0
		AND     parent_obj = object_id(@tablename)

	OPEN curs_constraints

	FETCH NEXT FROM curs_constraints INTO @constname
		WHILE (@@fetch_status = 0)
		BEGIN
			SELECT @cmd = 'ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @constname
			EXEC(@cmd)
			FETCH NEXT FROM curs_constraints INTO @constname
		END
	CLOSE curs_constraints
	DEALLOCATE curs_constraints
	RETURN 0
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


-- 4. Procedure to create log tables and triggers
--    Execute this procedure after inserting the table names for which log tables and triggers to be created
--    into tbl_Data_Tables

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE     PROCEDURE dbo.proc_Create_Log_Table_Trigger
AS
	-- Creates log tables and Trigger to the main tables.
	DECLARE @Table_Name sysname,
		@Log_Table sysname,
		@strSQL Varchar(8000)
	
	DECLARE  eRR_Data_Table Cursor FOR
		SELECT 	sobj.Name [Table_Name]  
		FROM 	[sysobjects] sobj
		WHERE 	sobj.Name NOT in ('tbl_Data_Tables','dtproperties')
			AND sobj.Name NOT IN (
				SELECT 	Table_Name 
				FROM 	tbl_Data_Tables  UNION 
				SELECT 	Log_Table_Name  AS Table_Name 
				FROM 	tbl_Data_Tables )
			AND sobj.xType = 'U'
		ORDER BY 1

	OPEN eRR_Data_Table
	FETCH FROM eRR_Data_Table INTO @Table_Name
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @Log_Table = @Table_Name + '_Log'
	      --Create New log Table
		SET @strSQL = '  SELECT * INTO [dbo].[' + @Log_Table + '] FROM [dbo].[' + @Table_Name  + '] WHERE 1 = 2  '
		EXEC(@strSQL)

      		-- Drop all Constraints
      		EXECUTE proc_drop_constraints  @Log_Table
	      	SET @strSQL =  ' Alter Table ' + @Log_Table + ' ADD  Activity Char(1)' 
      		EXEC(@strSQL)

		--Insert Data to maintain relation ship between log tables and main table
      		INSERT INTO tbl_Data_Tables(Table_Name,Log_Table_Name)
	      	SELECT @Table_Name,@Log_Table
      		
		Fetch Next from eRR_Data_Table INTO @Table_Name	
		--Create Trigger for main  table 
		SET @strSQL = 'CREATE TRIGGER dbo.[Update_LOG_' +  @Table_Name + '] ON [dbo].[' + @Table_Name + '] ' + CHAR(13)
		SET @strSQL = @strSQL + 'FOR INSERT,UPDATE,DELETE ' + CHAR(13)
		SET @strSQL = @strSQL + 'AS ' + CHAR(13)
		SET @strSQL = @strSQL + 'SELECT * INTO #Tmp_Inserted FROM Inserted ' + CHAR(13)
		SET @strSQL = @strSQL + 'SELECT * INTO #Tmp_Deleted FROM Deleted  ' + CHAR(13)
		SET @strSQL = @strSQL + 'exec proc_Insert_To_Log  [' + @Table_Name + '] ' + CHAR(13)
		SET @strSQL = @strSQL + 'Drop Table #Tmp_Inserted  ' + CHAR(13)	-- the temp tables are used in proc_Insert_To_Log
		SET @strSQL = @strSQL + 'Drop Table #Tmp_Deleted ' + CHAR(13)
		EXEC(@strSQL)
	END 
	CLOSE eRR_Data_Table
	DEALLOCATE eRR_Data_Table

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-- 5. Donot delete the data from table [tbl_Data_Tables] for the triggers to work

Rate

1.17 (6)

Share

Share

Rate

1.17 (6)