none
How to make log using trigger?

    Question

  • Dear all

    I have a database that in this database I have to record log of any changes in each table. To make this I designed 3 Tables. 1) TableMasterFile that contains list of all tables valid in Database 2)DataIdentityTable that save all Primarykeys value for each table 3)log that it save changes of any record for each table.

    Then I added a trigger on each table that will run for Insert, Update, Delete and it will save changes in the log table. Everything work correctly but problem is that if I active the log trigger it will make insert happen 12 times slower! So for an insertion that have to insert around 60 records it will take almost 2 minutes to insert and it is so slow.

    I put a trigger of insert so you can see how I made the trigger.

    Question is this: Is there anyway to make it faster?

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    
    
    ALTER TRIGGER [dbo].[GenerationOfBillingASIALog] 
       ON  [dbo].[GenerationOfBillingASIA] 
       AFTER INSERT
    AS 
    BEGIN
    	
    	SET NOCOUNT ON;
    
    
    	SELECT * INTO GenerationOfBillingASIAIns FROM INSERTED
    	SELECT * INTO GenerationOfBillingASIADel FROM DELETED
    	
    
        DECLARE @SQLQuery VARCHAR(Max)
    	DECLARE @PrimaryKeyID INT
    	DECLARE @PKColumn VARCHAR(250)
    	DECLARE	@TableName VARCHAR(128)
    	DECLARE @ColumnTable TABLE(ColumnName VARCHAR(250), Counter INT identity)
    	DECLARE @RowCounts AS INT
    	DECLARE @ColumnName AS VARCHAR(250)
    	DECLARE @i AS INT
    	
    	-- Name of the table that will use this Trigger.
    	SET @TableName = 'GenerationOfBilling'
    	
    	-- This will get the Primarykey column of the table used.
    	SET @PKColumn = (SELECT TOP 1 c.COLUMN_NAME 
    	FROM 	INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
    		INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    	WHERE 	pk.TABLE_NAME = @TableName
    	AND	CONSTRAINT_TYPE = 'PRIMARY KEY'
    	AND	c.TABLE_NAME = pk.TABLE_NAME
    	AND	c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)
    	
    	-- This will insert all columns of the table used in this trigger in the temp table for the Columns
    	INSERT INTO @ColumnTable
    	SELECT column_name 'ColumnName'
    	FROM information_schema.columns
    	WHERE table_name = @TableName + 'Ins'
    	
    
    	SET @SqlQuery = 'INSERT INTO DataIdentityTable
    	SELECT * FROM (
    	SELECT COALESCE(TMF.TableID,0) TableID, CAST(TI.[' + @PKColumn + '] AS VARCHAR) AS PrimaryKey FROM GenerationOfBillingASIAIns TI LEFT OUTER JOIN TableMasterFile TMF ON TMF.TableName = ''' + @TableName + '''
    	UNION
    	SELECT COALESCE(TMF.TableID,0) TableID, CAST(TD.[' + @PKColumn + '] AS VARCHAR) AS PrimaryKey FROM GenerationOfBillingASIADel TD LEFT OUTER JOIN TableMasterFile TMF ON TMF.TableName = ''' + @TableName + '''
    	) GenerationOfBillingASIA WHERE CAST(TableID AS VARCHAR) + ''' + 'SEPARATOR' + ''' + CAST(PrimaryKey AS VARCHAR) 
    	NOT IN (SELECT CAST(TableID AS VARCHAR) + ''' + 'SEPARATOR' + ''' + CAST(PrimaryKey AS VARCHAR) FROM DataIdentityTable)'
    	EXECUTE(@SqlQuery)
    
    	-- INSERT --
    	ELSE IF (SELECT COUNT(INSERTED.InvoiceNumber) FROM INSERTED) > 0
    	BEGIN	
    		-- This will check each column of the table used and Save all deleted values in each column into the Log table
    		-- along with the information needed. (tableID, columnName, primarykey, userID, date, etc...)
    		SET @i = 0
    		SET @RowCounts = (SELECT COUNT(*) AS COUNT FROM @ColumnTable)
    		While @i < @RowCounts
    		BEGIN
    		SET @i = @i+1
    		SET @ColumnName = (SELECT Top 1 ColumnName FROM @ColumnTable WHERE Counter = @i)
    		
    		SET @SQLQuery = 'INSERT INTO Log 
    				SELECT PK.PrimaryKeyID, '''+@ColumnName+''' AS [ColumnName],
    				NULL AS OldValue, CAST(TI.['+@ColumnName+'] AS VARCHAR(50)) AS NewValue, 
    				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM GenerationOfBillingASIAIns TI INNER JOIN 
    				(SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C LEFT OUTER JOIN TableMasterFile TMF 
    				ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = ''' + @TableName + ''' + CAST(TI.[' + @PKColumn + '] AS VARCHAR)'
    				
    		EXECUTE(@SQLQuery)
    		END
    	END
    
    
    	-- This will drop the physical copy of Inserted and Deleted tables created above.
    	DROP TABLE GenerationOfBillingASIAIns
    	DROP TABLE GenerationOfBillingASIADel
    END
    


    Nothing is Impossible

    Tuesday, April 03, 2012 7:55 AM

Answers

  • A question first: What is the DataIdentityTable for?

    The log insertion should be happen in only one SQL statement, something like:

    DECLARE @LogHeader TABLE ( ID INT ) ;
    DECLARE @LogHeaderID INT ;
    	
    INSERT  INTO LogHeader
            ( [Type], [UserID], [Date] )
    OUTPUT  INSERTED.ID
            INTO @LogHeader
    VALUES  ( 0, SYSTEM_USER, GETDATE() ) ;                    
    
    SELECT  @LogHeaderID = ID
    FROM    @LogHeader ;
                      
    INSERT  INTO LogDetail
            ( LogHeaderID ,
              ColumnName ,
              OldValue ,
              NewValue 
            )
            SELECT  @LogHeaderID ,
                    'BillingDate' ,
                    NULL ,
                    CAST(BillingDate AS NVARCHAR(MAX))
            FROM    INSERTED 
            UNION ALL
            SELECT  @LogHeaderID ,
                    'DueDate' ,
                    NULL ,
                    CAST(DueDate AS NVARCHAR(MAX))
            FROM    INSERTED 
            UNION ALL
            ... ;   



    Tuesday, April 03, 2012 2:45 PM

All replies

  • The use of dynamic SQL should be the source of your problem. Don't use it, it is not necessary: you know all facts, like primary key columns, when creating the trigger.

    Use the dynamic SQL to emit the trigger creation T-SQL code.

    Also don't create copies of DELETED and INSERTED, especially when you continue using both, the copy and the original tables.

    Also DELETED and its copy are not necessary in a AFTER INSERT trigger.

    You trigger code also contains an error: The ELSE before the IF. Use copy and past for posting code. Also ensure, when possible, that your code is correct and can be execute, especially when you say it works.

    When I read your code correctly, then your  logging all columns when the row is affected by an insert. In this case you should consider using a simple copy logging with a log table per tracked table, e.g.:

    ALTER TRIGGER [dbo].[GenerationOfBillingASIALog] ON [dbo].[GenerationOfBillingASIA]
        AFTER INSERT
    AS
        SET NOCOUNT ON ;
    
        INSERT  INTO [Log].[GenerationOfBillingASIA]
                ( ChangeType ,
                  <column_list> 
                )
                SELECT  'I' ,
                        <column_list>
                INTO    GenerationOfBillingASIAIns
                FROM    INSERTED ;

    Tuesday, April 03, 2012 8:35 AM
  • Dear Stefan

    Thank you so much for your fast reply. Actually I have only one log table to save all changes. bellow you can see the design of my log. My database has more than 200 Tables therefore I made one trigger to be used for all tables.

    The idea is that in log table I will save what column had change and how was the change.

    TableMasterFile:

    TableID(PK, int, not null), TableName(varchar(100), not null), CreatedDate(datetime, not null)

    DataIdentityTable:

    PrimaryKeyID (PK, int, not null), TableID (FK, int, not null), PrimaryKeyValue(varchar(25), not null)

    log:

    LogID(PK, int, not null), PrimaryKeyID(FK, int, not null) ColumnName(varchar(50), not null), OldValue(varchar(max), null), NewValue(varchar(max), null), Type(smallint, not null) UserID(varchar(20), not null), Date(datetime, not null)

    as you can see in Log table I save the name of the column the old and new value and the type of change that if it was insert, update or delete.


    Nothing is Impossible

    Tuesday, April 03, 2012 9:59 AM
  • Here are some pointers we can try of

    1. Avoid using Information Schema tables. Instead you can use sp_pkeys or use other system tables to get the primay key columns

    2. Modify the IF ELSE part with the below code. This code will be construct all the Insert statements in one shot and execute. Rather than running a loop.

    		SELECT @SQLQuery = @SQLQuery + CHAR(13) + 'INSERT INTO Log 
    				SELECT PK.PrimaryKeyID, '''+ C.ColumnName +''' AS [ColumnName],
    				NULL AS OldValue, CAST(TI.['+C.ColumnName +'] AS VARCHAR(50)) AS NewValue, 
    				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM GenerationOfBillingASIAIns TI INNER JOIN 
    				(SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C LEFT OUTER JOIN TableMasterFile TMF 
    				ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = ''' + @TableName + ''' + CAST(TI.[' + @PKColumn + '] AS VARCHAR)' + CHAR(13)
    		  FROM @ColumnTable C
    		EXECUTE(@SQLQuery)

    Note : The logic you have written may fail for the table with composite primary keys.
    Tuesday, April 03, 2012 10:25 AM
  • Actually I have only one log table to save all changes. bellow you can see the design of my log. My database has more than 200 Tables therefore I made one trigger to be used for all tables.

    Imho this concept is not tenable, when you want to optimize performance.. To be precise: One log table may fit your problem, but a generic trigger using dynamic SQL will not.

    Tuesday, April 03, 2012 11:06 AM
  • Dear Stefan

    I changed the query to the query bellow but it still take such a long time to insert! almost same as old one!

    Anyway Thank you so much

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    CREATE TRIGGER [dbo].[GenerationOfBillingASIALog2] 
       ON  [dbo].[GenerationOfBillingASIA] 
       AFTER INSERT
    AS 
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	DECLARE	@TableName VARCHAR(128)
    	DECLARE	@PrimaryKey VARCHAR(128)
    	DECLARE	@TableID VARCHAR(128)
    	-- Name of the table that will use this Trigger.
    	SET @TableName = 'GenerationOfBillingASIA'
    	--INSERT INTO DataIdentityTable (TableID and PrimaryKey)
    	SET @TableID = (Select Top 1 TableID FROM TableMasterFile Where TableName = @TableName)
    	INSERT INTO DataIdentityTable SELECT @TableID as [TableID], InvoiceNumber as PrimaryKey From INSERTED
    	--INSERT INTO LOG
    	INSERT INTO Log 
    				SELECT PK.PrimaryKeyID, 'InvoiceNumber' AS [ColumnName],
    				'NULL' AS OldValue, CAST(TI.[InvoiceNumber] AS VARCHAR(50)) AS NewValue, 
    				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
    				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
    				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.[InvoiceNumber] AS VARCHAR)
    	INSERT INTO Log 
    				SELECT PK.PrimaryKeyID, 'BillingDate' AS [ColumnName],
    				'NULL' AS OldValue, CAST(TI.BillingDate AS VARCHAR(50)) AS NewValue, 
    				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
    				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
    				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.BillingDate AS VARCHAR)
    	INSERT INTO Log 
    				SELECT PK.PrimaryKeyID, 'DueDate' AS [ColumnName],
    				'NULL' AS OldValue, CAST(TI.DueDate AS VARCHAR(50)) AS NewValue, 
    				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
    				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
    				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.DueDate AS VARCHAR)
    				
    	INSERT INTO Log 
    				SELECT PK.PrimaryKeyID, 'Particular' AS [ColumnName],
    				'NULL' AS OldValue, CAST(TI.Particular AS VARCHAR(50)) AS NewValue, 
    				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
    				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
    				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.Particular AS VARCHAR)
    				
    	INSERT INTO Log 
    				SELECT PK.PrimaryKeyID, 'PrincipalAmount' AS [ColumnName],
    				'NULL' AS OldValue, CAST(TI.PrincipalAmount AS VARCHAR(50)) AS NewValue, 
    				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
    				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
    				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.PrincipalAmount AS VARCHAR)
    				
    	INSERT INTO Log 
    				SELECT PK.PrimaryKeyID, 'RestructuredAmount' AS [ColumnName],
    				'NULL' AS OldValue, CAST(TI.RestructuredAmount AS VARCHAR(50)) AS NewValue, 
    				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
    				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
    				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.RestructuredAmount AS VARCHAR)
    				
    	INSERT INTO Log 
    				SELECT PK.PrimaryKeyID, 'ReferencePenalty' AS [ColumnName],
    				'NULL' AS OldValue, CAST(TI.ReferencePenalty AS VARCHAR(50)) AS NewValue, 
    				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
    				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
    				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.ReferencePenalty AS VARCHAR)
    				
    	INSERT INTO Log 
    				SELECT PK.PrimaryKeyID, 'Balance' AS [ColumnName],
    				'NULL' AS OldValue, CAST(TI.Balance AS VARCHAR(50)) AS NewValue, 
    				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
    				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
    				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.Balance AS VARCHAR)
    				
    	INSERT INTO Log 
    				SELECT PK.PrimaryKeyID, 'BStatus' AS [ColumnName],
    				'NULL' AS OldValue, CAST(TI.BStatus AS VARCHAR(50)) AS NewValue, 
    				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
    				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
    				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.BStatus AS VARCHAR)
    				
    	INSERT INTO Log 
    				SELECT PK.PrimaryKeyID, 'PostingDate' AS [ColumnName],
    				'NULL' AS OldValue, CAST(TI.PostingDate AS VARCHAR(50)) AS NewValue, 
    				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
    				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
    				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.PostingDate AS VARCHAR)
    				
    	INSERT INTO Log 
    				SELECT PK.PrimaryKeyID, 'SourceMode' AS [ColumnName],
    				'NULL' AS OldValue, CAST(TI.SourceMode AS VARCHAR(50)) AS NewValue, 
    				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
    				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
    				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.SourceMode AS VARCHAR)
    				
    	INSERT INTO Log 
    				SELECT PK.PrimaryKeyID, 'ReferenceRestructuring' AS [ColumnName],
    				'NULL' AS OldValue, CAST(TI.ReferenceRestructuring AS VARCHAR(50)) AS NewValue, 
    				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
    				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
    				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.ReferenceRestructuring AS VARCHAR)
    				
    	INSERT INTO Log 
    				SELECT PK.PrimaryKeyID, 'TransactionCode' AS [ColumnName],
    				'NULL' AS OldValue, CAST(TI.TransactionCode AS VARCHAR(50)) AS NewValue, 
    				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
    				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
    				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.TransactionCode AS VARCHAR)
    				
    	INSERT INTO Log 
    				SELECT PK.PrimaryKeyID, 'InterestAmount' AS [ColumnName],
    				'NULL' AS OldValue, CAST(TI.InterestAmount AS VARCHAR(50)) AS NewValue, 
    				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM INSERTED TI 
    				INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C 
    				LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = '''' + @TableName + '''' + CAST(TI.InterestAmount AS VARCHAR)
    				
    
    END
    GO


    Nothing is Impossible

    Tuesday, April 03, 2012 12:27 PM
  • Thank you so much Sorna

    I tried your code but it didn't change the speed. :)



    Nothing is Impossible

    Tuesday, April 03, 2012 12:28 PM
  • A question first: What is the DataIdentityTable for?

    The log insertion should be happen in only one SQL statement, something like:

    DECLARE @LogHeader TABLE ( ID INT ) ;
    DECLARE @LogHeaderID INT ;
    	
    INSERT  INTO LogHeader
            ( [Type], [UserID], [Date] )
    OUTPUT  INSERTED.ID
            INTO @LogHeader
    VALUES  ( 0, SYSTEM_USER, GETDATE() ) ;                    
    
    SELECT  @LogHeaderID = ID
    FROM    @LogHeader ;
                      
    INSERT  INTO LogDetail
            ( LogHeaderID ,
              ColumnName ,
              OldValue ,
              NewValue 
            )
            SELECT  @LogHeaderID ,
                    'BillingDate' ,
                    NULL ,
                    CAST(BillingDate AS NVARCHAR(MAX))
            FROM    INSERTED 
            UNION ALL
            SELECT  @LogHeaderID ,
                    'DueDate' ,
                    NULL ,
                    CAST(DueDate AS NVARCHAR(MAX))
            FROM    INSERTED 
            UNION ALL
            ... ;   



    Tuesday, April 03, 2012 2:45 PM
  • Some of my PrimaryKeys are varchar and some up to 50 char! so reduce space usage I put the primarykey in DataIdentityTable so that instead of those varchar i use an integer and save the space :)

    Anyway I did the same as you said but I mixed it with the answer of Soran and I got the fast query that can insert in less than one second :)

    Also may I ask to explain this part of your code? I am not pro to understand it well :)

    DECLARE @LogHeader TABLE ( ID INT ) ;
    DECLARE @LogHeaderID INT ;
    	
    INSERT  INTO LogHeader
            ( [Type], [UserID], [Date] )
    OUTPUT  INSERTED.ID
            INTO @LogHeader
    VALUES  ( 0, SYSTEM_USER, GETDATE() ) ;                    
    
    SELECT  @LogHeaderID = ID
    FROM    @LogHeader ;

    Thank you so much for your help


    Nothing is Impossible

    Wednesday, April 04, 2012 5:42 AM
  • This is what I used:

    SET @SQLQuery = ''
    SELECT @SQLQuery = @SQLQuery + 'UNION' +CHAR(13) + 'SELECT TI.['+ @PKColumn +'], '''+ C.ColumnName +''' AS [ColumnName],
    				NULL AS OldValue, CAST(TI.['+C.ColumnName +'] AS VARCHAR(50)) AS NewValue, 
    				0 AS [Type], system_user AS [UserID], getdate() AS [Date] FROM GenerationOfBillingASIAIns TI ' + CHAR(13)
    		  FROM @ColumnTable C
    Set @SQLQuery = SUBSTRING(@SQLQuery, 6, Len(@SQLQuery))
    SET @SQLQuery = 'INSERT INTO [LOG]
    			Select PK.PrimaryKeyID, [ColumnName], OldValue, NewValue, [Type], [UserID], [Date] from (' + @SQLQuery
    SET @SQLQuery = @SQLQuery + ') A INNER JOIN (SELECT PrimaryKeyID, TableName, PrimaryKey FROM DataIdentityTable C LEFT OUTER JOIN TableMasterFile TMF ON C.TableID = TMF.TableID) PK ON PK.TableName + PrimaryKey = ''' + @TableName + ''' + CAST(A.[' + @PKColumn + '] AS VARCHAR)'
    


    Nothing is Impossible

    Wednesday, April 04, 2012 5:45 AM
  • This is just the generic table layout for a column based storage in a log table.

    You need a master table which holds the information when, by-whom and what (kind of operation) occurred. This is my LogHeader table. The column values are stored in a detail table, here called LogDetail. btw, in my example there is the primary key column missing in that table.

    The way you're reading the inserted row and its new ID is error prone to parallel processing.

    Wednesday, April 04, 2012 8:52 AM