x

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
more ▼

asked Jul 28, 2011 at 04:52 AM in Default

karthik gravatar image

karthik
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Jul 28, 2011 at 05:20 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Thanks for this Pavel.

But It returns the column names incorrectly... changes need in WHERE condition?
Jul 28, 2011 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, 2011 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, 2011 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, 2011 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, 2011 at 08:47 AM karthik
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x21

asked: Jul 28, 2011 at 04:52 AM

Seen: 2327 times

Last Updated: Jul 28, 2011 at 04:52 AM