Tuesday, September 29, 2009

SQL Trigger to capture all the columns that are getting updated while UPDATE

CREATE TABLE [dbo].[Table1](
    [id] [int] NOT NULL,
    [ColumnA] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [namechanged] [int] NULL
) ON [PRIMARY]





ALTER TRIGGER [table1_update] ON [dbo].[Table1]
FOR  UPDATE
AS begin
DECLARE @ColID INT
DECLARE @Cols VARCHAR(8000)
SET @Cols = SPACE(0)
SET @ColID = 1
WHILE @ColID <= (SELECT COUNT(*)
                  FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_NAME = 'Table1')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@ColID - 1) / 8 + 1, 1))  &
               POWER(2, (@ColID - 1) % 8) =
               POWER(2, (@ColID - 1) % 8)
   SELECT @Cols = @Cols + COLUMN_NAME + ','
                 FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_NAME = 'Table1' AND ORDINAL_POSITION = @ColID

   SET @ColID = @ColID + 1
END
PRINT 'Updated columns are :' + @Cols
END

No comments:

Post a Comment