Implementing generic audit trail trigger in sql server

CREATE TABLE [dbo].[Audit](
	[TYPE] [CHAR](1) NULL,
	[TableName] [VARCHAR](128) NULL,
	[PK] [VARCHAR](1000) NULL,
	[FieldName] [VARCHAR](128) NULL,
	[OldValue] [VARCHAR](1000) NULL,
	[NewValue] [VARCHAR](1000) NULL,
	[UpdateDate] [datetime] NULL,
	[UserName] [VARCHAR](128) NULL
) ON [PRIMARY]

CREATE TRIGGER [dbo].[xxx] ON [dbo].[yyy] FOR INSERT, UPDATE, DELETE
AS
 
DECLARE @bit INT ,
	@FIELD INT ,
	@maxfield INT ,
	@CHAR INT ,
	@fieldname VARCHAR(128) ,
	@TableName VARCHAR(128) ,
	@PKCols VARCHAR(1000) ,
	@SQL VARCHAR(2000), 
	@UpdateDate VARCHAR(21) ,
	@UserName VARCHAR(128) ,
	@TYPE CHAR(1) ,
	@PKSelect VARCHAR(1000)
 
	SELECT @TableName = 'YourTableName'
 
	-- date and user
	SELECT 	@UserName = system_user ,
		@UpdateDate = CONVERT(VARCHAR(8), getdate(), 112) + ' ' + CONVERT(VARCHAR(12), getdate(), 114)
 
	-- Action
	IF EXISTS (SELECT * FROM inserted)
		IF EXISTS (SELECT * FROM deleted)
			SELECT @TYPE = 'U'
		ELSE
			SELECT @TYPE = 'I'
	ELSE
		SELECT @TYPE = 'D'
 
	-- get list of columns
	SELECT * INTO #ins FROM inserted
	SELECT * INTO #del FROM deleted
 
	-- Get primary key columns for full outer join
	SELECT	@PKCols = COALESCE(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + 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
 
	-- Get primary key select for insert
	SELECT @PKSelect = COALESCE(@PKSelect+'+','') + '''<' + COLUMN_NAME + '=''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + 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
 
	IF @PKCols IS NULL
	BEGIN
		raiserror('no PK on table %s', 16, -1, @TableName)
		RETURN
	END
 
	SELECT @FIELD = 0, @maxfield = MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
	while @FIELD < @maxfield
	BEGIN
		SELECT @FIELD = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @FIELD
		SELECT @bit = (@FIELD - 1 )% 8 + 1
		SELECT @bit = POWER(2,@bit - 1)
		SELECT @CHAR = ((@FIELD - 1) / 8) + 1
		IF SUBSTRING(COLUMNS_UPDATED(),@CHAR, 1) & @bit > 0 OR @TYPE IN ('I','D')
		BEGIN
			SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION = @FIELD
			SELECT @SQL = 		'insert Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)'
			SELECT @SQL = @SQL + 	' select ''' + @TYPE + ''''
			SELECT @SQL = @SQL + 	',''' + @TableName + ''''
			SELECT @SQL = @SQL + 	',' + @PKSelect
			SELECT @SQL = @SQL + 	',''' + @fieldname + ''''
			SELECT @SQL = @SQL + 	',convert(varchar(1000),d.' + @fieldname + ')'
			SELECT @SQL = @SQL + 	',convert(varchar(1000),i.' + @fieldname + ')'
			SELECT @SQL = @SQL + 	',''' + @UpdateDate + ''''
			SELECT @SQL = @SQL + 	',''' + @UserName + ''''
			SELECT @SQL = @SQL + 	' from #ins i full outer join #del d'
			SELECT @SQL = @SQL + 	@PKCols
			SELECT @SQL = @SQL + 	' where i.' + @fieldname + ' <> d.' + @fieldname 
			SELECT @SQL = @SQL + 	' or (i.' + @fieldname + ' is null and  d.' + @fieldname + ' is not null)' 
			SELECT @SQL = @SQL + 	' or (i.' + @fieldname + ' is not null and  d.' + @fieldname + ' is null)' 
			EXEC (@SQL)
		END
	END