CREATE TABLE [dbo].[Table1](
[id] [int] NOT NULL,
[ColumnA] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[namechanged] [int] NULL
) ON [PRIMARY]
[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
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