COLUMNS_UPDATED() - Not Returning the Column Name

Question

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

Answer 1

0

if you are running on SQL Server 2008 then MSDN COLUMNS_UPDATED (Transact-SQL) 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

answered Jul 28 '11 at 05:20 AM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

Thanks for this Pavel.

But It returns the column names incorrectly... changes need in WHERE condition?

Jul 28 '11 at 06:57 AM karthik

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?

Jul 28 '11 at 07:03 AM Pavel Pawlowski

Yes.For Test table, I am also getting the column names correctly. But I added this piece of code to main tables trigger its not returning the correct column names.

Jul 28 '11 at 07:40 AM karthik

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?

Jul 28 '11 at 08:14 AM Pavel Pawlowski

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

Jul 28 '11 at 08:47 AM karthik

(comments are locked)

COLUMNS_UPDATED() - Not Returning the Column Name

Follow this question

Related Questions