set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[tr_test_update]  
   ON  [dbo].[TBL_BACKUPTEST]
   FOR UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @ObjName VARCHAR(100),
			@TableName VARCHAR(100),
			@Cols NVARCHAR(4000),
			@sql NVARCHAR(MAX),
			@Data NVARCHAR(MAX),
			@ParmDefinition nvarchar(500),
			@Outputparam varchar(MAX);
	SET @TableName = 'TBL_BACKUPTEST'
	SELECT @Cols = 
			(
			SELECT '+''|''+CASE WHEN [' +c.name+ '] IS NULL THEN ''NULL'' ELSE CAST([' + c.name + '] AS VARCHAR(MAX)) END'
			FROM DataFX.sys.columns c
			WHERE c.[object_id] = object_Id(@TableName)
			FOR XML PATH(''),TYPE
			).value('.','VARCHAR(MAX)')

	SET @sql = N'SELECT @Data = ' + @Cols + ' FROM ' +@TableName+ ' b JOIN inserted as i on i.ROWID = b.ROWID'  
	SET @ParmDefinition = N'@Data varchar(MAX) OUTPUT';

	EXECUTE sp_executesql @sql, @ParmDefinition, @Data=@Outputparam OUTPUT;

    -- Insert statements for trigger here
	INSERT INTO dbo.TBL_BACKUP ([TABLE],[ACTION],ROWID,DATA)
    SELECT 'TBL_BACKUPTEST' AS [TABLE],'UPDATE' AS [ACTION],ROWID,@Outputparam FROM INSERTED
END