Skip to content

Create SQL trigger that will log specific fields changed in log table

April 19, 2012
CREATE TRIGGER [t_Test] ON [tbl_Test]
	AFTER INSERT
AS
	SET NOCOUNT ON
		
	DECLARE	@Cursor CURSOR ,
		@Column NVARCHAR(50) ,
		@SQL VARCHAR(MAX)

	SELECT	*
	INTO	##New
	FROM	INSERTED;
		
	WITH	a AS ( SELECT	*, ROW_NUMBER() OVER ( ORDER BY ChangeTime DESC ) AS rn
				   FROM		dbo.tbl_Test
				   WHERE	[System] IN ( SELECT	[System]
										  FROM		INSERTED ))
		SELECT	*
		INTO	##Old
		FROM	a
		WHERE	rn = 2

	SET @Cursor = CURSOR FORWARD_ONLY STATIC FOR
			SELECT	[name]
			FROM	sys.columns
			WHERE	OBJECT_ID = ( SELECT	OBJECT_ID
								  FROM		sys.objects
								  WHERE		[name] = 'tbl_Test' )
					AND [name] <> 'DataID'
					AND [name] <> 'ChangeDate'
					AND [name] <> 'ChangedBy'
					AND [name] <> 'System'  

	OPEN @Cursor
	FETCH NEXT FROM @Cursor INTO @Column

	WHILE @@FETCH_STATUS = 0 
		BEGIN
			SET @SQL = N'INSERT INTO tbl_Test_ChangeLog 
					SELECT DISTINCT	p1.ChangeDate, p1.ChangedBy, p1.System, 
						''' + @Column + ''' Field, p1.[' + @Column + '] OldValue, p2.[' + @Column + '] NewValue
						FROM ##Old p1
						INNER JOIN ##New p2 ON p1.System = p2.System 
						WHERE	p1.System <> '''' AND p1.[' + @Column + '] <> p2.[' + @Column + ']'
				--PRINT @SQL
			EXEC (@SQL)

			FETCH NEXT FROM @Cursor INTO @Column
		END

	CLOSE @Cursor
	DEALLOCATE @Cursor
		  
	DROP TABLE ##New
	DROP TABLE ##Old
Advertisements

From → SQL

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: