Technical Article

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_Namesysname
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_NameNVarchar(200),
@IdentityRowCountinteger

-- 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
@tablenamesysname
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 @constnamesysname,
@cmdvarchar(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)