Showing posts with label Trigger. Show all posts
Showing posts with label Trigger. Show all posts

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

SQL Trigger to capture OLD and NEW values while updating in TABLE

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

GO

CREATE TRIGGER [table1_update] ON [dbo].[Table1]
FOR  UPDATE
AS begin
  update table1 set table1.namechanged = table1.namechanged + 1
  from inserted i, deleted d
  where i.[ColumnA] <> d.[ColumnA] and i.[id] = d.[id] and table1.[id] = i.[id]
end