Tuesday, January 8, 2008

Columns_Updated() within a trigger

Happy New Year to everyone. Once someone asked me about what columns are affected by the UPDATE statement. As you probaly know that in SQL Server 2005 we can do that in more elegant way, but for SQL Server 2000 I decided perfrom some testing.

CREATE TABLE Test (c1 INT NOT NULL PRIMARY KEY, c2 CHAR(1),c3 INT)
GO
--Insert some data
INSERT INTO Test VALUES (1,'A',100)
INSERT INTO Test VALUES (2,'B',500)

--Create a trigger
CREATE TRIGGER tr_my_trigger ON Test FOR UPDATE
AS
DECLARE @ColumnID INT
DECLARE @Columns VARCHAR(8000)
SET @Columns = SPACE(0)
SET @ColumnID = 1
WHILE @ColumnID <= (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Test')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@ColumnID - 1) / 8 + 1, 1)) &
POWER(2, (@ColumnID - 1) % 8) =
POWER(2, (@ColumnID - 1) % 8)
SET @Columns = @Columns + CAST(@ColumnID AS VARCHAR) + ','
SET @ColumnID = @ColumnID + 1
END
PRINT 'Updated columns are :' + @Columns

---
UPDATE Test SET c2='D',c3=2000 WHERE c1=1
--Updated columns are :2,3
We see that the second and the third column were affected according to the INFORMATION_SCHEMA.COLUMNS ordered by ORDINAL_POSITION (check it out). It is not comfortable to see those numbers , but with a little effort you can get also name of columns from the INFORMATION_SCHEMA.COLUMNS.

I suggest you not to run/use that on the production server as it may hurt performance.

12 comments:

Razvan Socol said...

Here is a faster trigger, which also provides the column names and takes into account the possible gaps in the sequence of ColumnID-s:

CREATE TRIGGER ShowUpdatedColumns ON Test FOR UPDATE
AS
DECLARE @ColumnID int, @Columns nvarchar(4000), @ObjectID int, @LastColumnID int
SET @ObjectID=(SELECT id FROM sysobjects WHERE name='test')
SET @LastColumnID=(SELECT MAX(colid) FROM syscolumns WHERE id=@ObjectID)
SET @ColumnID=1
WHILE @ColumnID<=@LastColumnID BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@ColumnID - 1) / 8 + 1, 1)) &
POWER(2, (@ColumnID - 1) % 8) = POWER(2, (@ColumnID - 1) % 8)
SET @Columns = ISNULL(@Columns+',','') + COL_NAME(@ObjectID,@ColumnID)
SET @ColumnID=@ColumnID+1
END
PRINT 'Updated columns are :' + @Columns

For example, test both triggers on the following table:

CREATE TABLE Test (c1 INT NOT NULL PRIMARY KEY, x int, c2 CHAR(1))
ALTER TABLE Test DROP COLUMN x
ALTER TABLE Test ADD c3 INT

Using the following code:

DECLARE @i int, @t datetime
SET @i=1
SET @t=GETDATE()
WHILE @i<1000 BEGIN
BEGIN TRAN
UPDATE Test SET c2='C', C3=200 WHERE c1=1
UPDATE Test SET c1=10 WHERE c1=1
UPDATE Test SET c1=10,c2='C' WHERE c1=1
ROLLBACK
SET @i=@i+1
END
SELECT DATEDIFF(ms,@t,GETDATE())

Razvan

Uri Dimant said...

Thanks for posting the script Razvan.

satish said...

As part of auditing, we need to capture the old and new values of only changed fields.
As per this script for column-updated(), it gives the columns list that are included in an update statment, but it will not indicate if the value that is being updated is a
actually a changed value or the same value. Refered the scripts of Pop Rivett for this.
Is there any other efficient way to can capture this.

xiaoqiu said...

Delivering a new dimension for hardcore Maple Story players with the addition of a fourth job and some free maple mesos, this is launched today. Maple Story players will have reached a high enough level to achieve the 4th Job or some necessary maplestory mesos, which begins this week. Many people can cost their own cheap mesos to gain themed dessert and costumes among their many posts. The addition of the 4th Job provides further depth to an already intricate and complex maple story mesos in game. At Leafre, players can find new upgrades for some of the games more powerful weapons can receive from mesos and also party with other elite players.

Replica Watches said...

29047126483369175 I play dofus Replica Watches for one year, I Replica Rolex Watches want to get some Replica Watch kamas to buy Replica Chanel Watches item for my character. So, I search "Replica Swiss Watches" on google and found many website. As Exact Replica Graham Watch the tips from the forum, I just review the Swiss Replica Watches websites and choose some Replica Montblanc Watches quality sites to Replica Cartier Watches compare the price, and go to their Replica Breguet Watches online support to make Replica Breitling Watches the test. And Last Chaos Gold I decide to use Replica BRM Watch at the end. And Tag Heuer Replica Watch that is the Replica IWC Watch beginning..

aiya said...

Office 2010is powerful!
Microsoft Office 2010is the best software in the world.
Microsoft wordis so great!
Office 2007makes life great!
Many people likeMicrosoft Office.
Microsoft Office 2007is welcomed by the whole world.
Office 2007 keyis available here.
Office 2007 downloadis on sale now!
Office 2007 Professionalbring me so much convenience.
Outlook 2010 make life wonderful!
Microsoft outlook is my love.
Microsoft outlook 2010 is convenient!
Windows 7 is convenient!

Frozen Juegos said...

I would like to thank you for your nicely written post.
Signature:
Jugar juegos de frozen en línea gratis, los nuevos de princesa de Disney juegos frozen - la princesa encantadora y linda. Divertirse frozen!

Facebook Descargar said...

Very interesting! Thanks you.
Signature:
download descargar facebook gratis para Android celular and download free descargar facebook apk and descargar facebook gratis , descarga facebook

Whatsapp Descargar said...

What a great online source of information about this topic. you have done great work. keep continue to sharing such kinds of post. keep it up.
Signature:
download free Descargar Whatsapp Para Android and download baixar whatsapp para celular online and descargar whatsapp , baixar whatsapp gratis

Anh Mai said...

Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.
Signature:
Versión en facebook en español descargar a los países hablan Español: facebook entrar direto agora , facebook en español descargar , facebook entrar direto agora

games unblocked said...

Thank you for that information you article
kids games
friv
unblockedgames
un show mas

Mít Trang said...

Thank you for that information you article
juegosjuegos
juegos de zombies
jogos de friv