MSSQL 2k trigger freak behavior -columns_updated()

robsevcik · November 19th, 2003, 08:57 AM

Good life,

I have a trouble - when i create table with more than 24 columns (may be it depends) the columns_updated() function returns zero - 0.

May be it is an error of MSSQL engine, may be I'm stupid.

It seems, that only 3B can be addressed :))

I need to use it and don't know about any alternative except of brutal programming :)

Thank you for your interest.

I've made an test example:

Code:

-----------------------------------
/*
if number of columns is higher than 24 (may be it depends)
trigger prints, that no columns were changed
test it with adding or remowing columns
*/


begin tran

CREATE TABLE [dbo].[TestCU] (
 [ID] [int] NOT NULL Identity (1,1) PRIMARY KEY,
 [C01] [int] NULL ,
 [C02] [int] NULL ,
 [C03] [int] NULL ,
 [C04] [int] NULL ,
 [C05] [int] NULL ,
 [C06] [int] NULL ,
 [C07] [int] NULL ,
 [C08] [int] NULL ,
 [C09] [int] NULL ,
 [C10] [int] NULL ,
 [C11] [int] NULL ,
 [C12] [int] NULL ,
 [C13] [int] NULL ,
 [C14] [int] NULL ,
 [C15] [int] NULL ,
 [C16] [int] NULL ,
 [C17] [int] NULL ,
 [C18] [int] NULL ,
 [C19] [int] NULL ,
 [C20] [int] NULL ,
 [C21] [int] NULL ,
 [C22] [int] NULL ,
 [C23] [int] NULL ,
 [C24] [int] NULL ,
/*
 [C25] [int] NULL ,
 [C26] [int] NULL ,
 [C27] [int] NULL ,
 [C28] [int] NULL ,
 [C29] [int] NULL ,
*/

 [CLast] [int] NULL

)

GO
CREATE TRIGGER [trTestCU] ON dbo.TestCU
FOR INSERT, UPDATE, DELETE
AS if columns_updated()>0 print 'YES!, columns were changed'
   else print 'NO!, columns were NOT changed !!! ???'
GO


insert into TestCU (C01,C02,C03,C04,C05,C06) values
(100,65840,15,2003,11,55)

rollback tran

-- [email protected]

Jeff Mason · November 19th, 2003, 07:23 PM

Um, you are aware that the columns_updated() function returns a bit mask, aren't you?

Each bit in the result is set if the corresponding column has been updated. The first column in the table is the leftmost bit, the second is the next, and so on, with the least significant bit of the result corresponding to the last column of the table.

When you do an INSERT, all columns are considered updated. Thus, the first column is updated, and this will be the most significant bit of the result. When this result is interpreted as an integer (when you compare it to zero) since the high order bit is set, the value is considered negative, which, of course, isn't greater than zero, which is why your trigger is misleading you.

Compare to exactly zero, and I think you'll be OK...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com