Create Log tables and Triggers

-- 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