Audit Table For Changed Values Only 1

[PGO01]

I am desperately trying to get an audit solution working with only changed values being sent to the audit table. Here is waht I have so far - can anyone please help with the two lines I've pointed out?

-- Drop objects if they already exist.
IF OBJECT_ID('TestTrigger_Update') IS NOT NULL DROP TRIGGER TestTrigger_Update
IF OBJECT_ID('TestTable') IS NOT NULL DROP TABLE TestTable
IF OBJECT_ID('TestTableAudit') IS NOT NULL DROP TABLE TestTableAudit;
GO

-- Create a test table.
CREATE TABLE TestTable(TestTableID INT IDENTITY (1, 1), MyCol1 INT, MyCol2 INT, MyCol3 INT, MyCol4 INT);
GO

-- Insert a record.
INSERT INTO TestTable
	SELECT 9, 8, 7, 6
GO

-- Create the audit table.
CREATE TABLE TestTableAudit(
	TestTableAuditID INT IDENTITY (1, 1),
	TableName NVARCHAR(200),
	RecordID INT, 
	ColumnName NVARCHAR(200),
	OldValue NVARCHAR(MAX),
	NewValue NVARCHAR(MAX))
GO

-- Create the update trigger.
CREATE TRIGGER TestTrigger_Update ON TestTable FOR UPDATE AS
BEGIN
	DECLARE @ColumnsUpdated VARBINARY(MAX)
	DECLARE @ColumnName NVARCHAR(200)
	DECLARE @OldValue NVARCHAR(MAX)
	DECLARE @NewValue NVARCHAR(MAX)

	SET @ColumnsUpdated = COLUMNS_UPDATED()
        
        -- Loop through the columns that have changed.
	DECLARE ChangedColumnCursor CURSOR FAST_FORWARD FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TestTable' AND sys.fn_IsBitSetInBitmask(@ColumnsUpdated, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')) <> 0
	OPEN ChangedColumnCursor
		FETCH NEXT FROM ChangedColumnCursor INTO @ColumnName
		WHILE @@FETCH_STATUS = 0
		BEGIN
			-- TODO: Set the @OldValue and @NewValue from the Inserted and Deleted respectively.
			-- How do I do this... ???
			sp_executesql N'SET @Old = SELECT ' + @ColumnName + N' FROM Deleted', N'@Old NVARCHAR(MAX) OUT', @Old = @OldValue OUTPUT
			sp_executesql N'SET @New = SELECT ' + @ColumnName + N' FROM Inserted', N'@New NVARCHAR(MAX) OUT', @New = @NewValue OUTPUT

			INSERT INTO TestTableAudit (TableName, RecordID, ColumnName, OldValue, NewValue) SELECT 
				'TestTable',
				Inserted.TestTableID,
				@ColumnName,
				@OldValue,
				@NewValue
			FROM Inserted FULL OUTER JOIN Deleted ON Inserted.TestTableID = Deleted.TestTableID

			FETCH NEXT FROM ChangedColumnCursor INTO @ColumnName
		END
	CLOSE ChangedColumnCursor
	DEALLOCATE ChangedColumnCursor
END
GO

-- Update a couple of columns.
UPDATE TestTable SET MyCol1 = 1234, MyCol3 = 1234
GO

-- Show the results.
SELECT * FROM TestTableAudit
GO

 

[bborissov]

(on top of my head and NOT tested)

CREATE TRIGGER TestTrigger_Update ON TestTable
       FOR UPDATE AS
BEGIN
    INSERT INTO TestTableAudit 
          (TableName, RecordID, ColumnName,
           OldValue, NewValue)
    SELECT 'TestTable',
           ISNULL(Inserted.TestTableID,Deleted.TestTableID)
           'MyCol1',
           Deleted.MyCol1,
           Inserted.MyCol1
    FROM Inserted
    FULL JOIN Deleted 
         ON Inserted.TestTableID = Deleted.TestTableID
    WHERE ISNULL(Inserted.MyCol1,'') <> ISNULL(Deleted.MyCol1,'') -- Put the right column type

    UNION

    SELECT 'TestTable',
           ISNULL(Inserted.TestTableID,Deleted.TestTableID)
           'MyCol2',
           Deleted.MyCol2,
           Inserted.MyCol2
    FROM Inserted
    FULL JOIN Deleted 
         ON Inserted.TestTableID = Deleted.TestTableID
    WHERE ISNULL(Inserted.MyCol2,'') <> ISNULL(Deleted.MyCol2,'') -- Put the right column type

    UNION

    SELECT 'TestTable',
           ISNULL(Inserted.TestTableID,Deleted.TestTableID)
           'MyCol3',
           Deleted.MyCol3,
           Inserted.MyCol3
    FROM Inserted
    FULL JOIN Deleted 
         ON Inserted.TestTableID = Deleted.TestTableID
    WHERE ISNULL(Inserted.MyCol3,'') <> ISNULL(Deleted.MyCol3,'') -- Put the right column type

    UNION

    SELECT 'TestTable',
           ISNULL(Inserted.TestTableID,Deleted.TestTableID)
           'MyCol4',
           Deleted.MyCol4,
           Inserted.MyCol4
    FROM Inserted
    FULL JOIN Deleted 
         ON Inserted.TestTableID = Deleted.TestTableID
    WHERE ISNULL(Inserted.MyCol4,'') <> ISNULL(Deleted.MyCol4,'') -- Put the right column type
END

Again, not tested!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[PGO01]

OK thanks Borislav.

So you're saying that I should forget the COLUMNS_UPDATED() and sys.fn_IsBitSetInBitmask() functions and do it manually! Going to be a pain because I have dozens of tables and some with many columns...

Dynamic SQL here I come :/

 

[bborissov]

I didn't said that
As I said that was the first thing that comes to my mind
But I think that manual work maybe is easier to maintain and maybe is a little faster.
No matter how many fields you have this is just a copy and paste and change the field name
But you should test both ways. Dynamic SQL and manual approach.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[PGO01]

Okay, I've put a little something together that creates the triggers for me, and all seems to be working well, so here's a star

*