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

avatar 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) 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

more ▼

answered Jul 28, 2011 at 05:20 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

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.

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:

x30

asked: Jul 28, 2011 at 04:52 AM

Seen: 3939 times

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

Copyright 2016 Redgate Software. Privacy Policy