SQL Trigger 來紀錄哪些欄位被修改了

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[TR_TestTable_Modify] on [dbo].[TestTable]

   AFTER UPDATE

AS

BEGIN

  DECLARE @DBTableID int;

  DECLARE @Columnid int;

  DECLARE @ColumnCount int;

  DECLARE @i int;

  DECLARE @ColumnName varchar(20);

  DECLARE @SQL varchar(4000);

  DECLARE @LogTime datetime;

  DECLARE @program nvarchar(200)





  SELECT @DBTableID=id from sysobjects where name='TestTable'

  SELECT @ColumnCount=MAX(column_id) FROM sys.columns WHERE [object_id]=@DBTableID

  SELECT @program=program_name from sys.dm_exec_sessions where session_id=@@SPID



  select * into #inserted from inserted;

  select * into #deleted from deleted;



  SET @i = 0

  SET @LogTime=GETDATE()



  WHILE @i < @ColumnCount

  BEGIN

   SET @i = @i+1



   SELECT @ColumnName=name FROM sys.columns WHERE [object_id]=@DBTableID AND column_id=@i

   SET @SQL = 'INSERT INTO LogTable SELECT a.invno,''‘TestTable'',''' + @ColumnName + ''',a.' + @ColumnName + ',b.'+ @ColumnName + ',''' + convert(varchar(30), @LogTime,121) + ''',''' + @program + ''' FROM #deleted a,#inserted b WHERE a.PK=b.PK AND a.' + @ColumnName + ' <> b.' + @ColumnName

   EXEC (@SQL)

  END



  drop table #inserted;

  drop table #deleted;



END

GO