asked
COLUMNS_UPDATED() - Not Returning the Column Name
I have created a trigger to returns the affected column names.[Trg_MyMaster] But Its not returning the last column name if I update the last column (Table Name is MyMaster]. But MyTest table is created from MyMaster and created the same Trigger. [Trg_MyTest] returns the last column name correctly if I update the last column in MyTest table. records and table structure are same between both table. Create TRIGGER [Trg_MyTest] ON [dbo].[MyTest] FOR UPDATE AS DECLARE @Columns_Updated VARCHAR(100) DECLARE @maxByteCU INT DECLARE @curByteCU INT Declare @TableName Varchar(100) Declare @field int Declare @maxfield int Declare @bit int Declare @char int Declare @fieldname varchar(128) Declare @Type char(1) Begin Print COLUMNS_UPDATED() Set @TableName='MyTest' Set @Type = 'U' select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName while @field < @maxfield begin select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field select @bit = (@field - 1 )% 8 + 1 select @bit = power(2,@bit - 1) select @char = ((@field - 1) / 8) + 1 if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 begin select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field Print @fieldname end end End
if you are running on SQL Server 2008 then MSDN [COLUMNS_UPDATED (Transact-SQL)][1] says: > In SQL Server 2008, the > ORDINAL_POSITION column of the > INFORMATION_SCHEMA.COLUMNS view is not > compatible with the bit pattern of > columns returned by COLUMNS_UPDATED. > To obtain a bit pattern compatible > with COLUMNS_UPDATED, reference the > ColumnID property of the > COLUMNPROPERTY system function when > you query the > INFORMATION_SCHEMA.COLUMNS view, as > shown in the following example.shown in the following example. Your trigger can be much more easier. On SQL Server you need to use column_id CREATE TRIGGER TRG_MyTest ON MyTest AFTER UPDATE AS BEGIN DECLARE @modifiedColumns nvarchar(max) SET @modifiedColumns = STUFF((SELECT ',' + name FROM sys.columns WHERE object_id = OBJECT_ID('dbo.MyTest') AND COLUMNS_UPDATED() & (POWER(2, column_id - 1)) <> 0 FOR XML PATH('')), 1, 1, '') PRINT @modifiedColumns END [1]:
http://msdn.microsoft.com/en-us/library/ms186329.aspx
I tested this on a test table I've created and it returned the columns correct. Only they are comma separated. Or you what you mean by incorrectly?
In the trigger is hard coded `OBJECT_ID('dbo.MyTest')` which always return columns of the test table. So did you modify the object name when creating the trigger for the main tables?
yes.. please see the below code i added DECLARE @modifiedColumns nvarchar(max) SET @modifiedColumns = STUFF((SELECT ',' + name FROM sys.columns WHERE object_id = OBJECT_ID(@TableName) AND COLUMNS_UPDATED() & (POWER(2, column_id - 1)) <> 0 FOR XML PATH('')), 1, 1, '') PRINT @modifiedColumns
