Below is excerpt from the BOL regarding column_bitmask regarding creation of triggers. I see a problem - if I could have upto 256 column per table and updated_bitmask and column_bitmask are Integer data type, then interger can only be no more then 2^32, but we need to have up to 2^(column number - 1) which could be up to 2^255. We will get overflow error.
Do I misunderstood something or there is a problem. We could use BIGINT but it only upto 2^62. We can not use any other data type because of bitwise comparison.
Pleas help.
Constantin
IF (COLUMNS_UPDATED())
Tests, in an INSERT or UPDATE trigger only, whether the mentioned column or columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit pattern that indicates which columns in the table were inserted or updated.
The COLUMNS_UPDATED function returns the bits in order from left to right, with the least significant bit being the leftmost. The leftmost bit represents the first column in the table; the next bit to the right represents the second column, and so on. COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than 8 columns, with the least significant byte being the leftmost. COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.
COLUMNS_UPDATED can be used anywhere inside the body of the trigger.
bitwise_operator
Is the bitwise operator to use in the comparison.
updated_bitmask
Is the integer bitmask of those columns actually updated or inserted. For example, table t1 contains columns C1, C2, C3, C4, and C5. To check whether columns C2, C3, and C4 are all updated (with table t1 having an UPDATE trigger), specify a value of 14. To check whether only column C2 is updated, specify a value of 2.
comparison_operator
Is the comparison operator. Use the equal sign (=) to check whether all columns specified in updated_bitmask are actually updated. Use the greater than symbol (>) to check whether any or some of the columns specified in updated_bitmask are updated.
column_bitmask
Is the integer bitmask of those columns to check whether they are updated or inserted.
trigger limitation column_bitmask
Kardi
GO
CREATE TABLE xyz
(Col1 INT, Col2 INT, Col3 INT, Col4 INT)
GO
CREATE TRIGGER tg_xyz
ON xyz
FOR UPDATE
AS
BEGIN
declare @Col smallint , @exp smallint , @byte smallint , @power int
select @Col = 4 , @exp = ( @Col - 1 ) % 8,
@byte = ( ( @Col - 1 ) / 8 ) + 1
if substring( columns_updated() , @byte , 1 ) & power( 2, @exp ) > 0
select 'Column #4 was updated'
END
GO
insert xyz values (1,2,3,4)
GO
UPDATE xyz
SET Col4 = 5
GO
ALTER TABLE xyz
DROP COLUMN Col4
ADD Col4 INT
GO
UPDATE xyz
SET Col4 = 5
GO
ALTER TRIGGER tg_xyz
ON xyz
FOR UPDATE
AS
BEGIN
DECLARE @TableName NVARCHAR(255)
SET @TableName = 'xyz'
FROM syscolumns
WHERE
id = OBJECT_ID(@TableName)
AND colid IN
(
SELECT
x.num
FROM
(
SELECT
(a.number * 256 + b.number) AS Num,
CASE (a.number * 256 + b.number) % 8
WHEN 0 THEN ((a.number * 256 + b.number) - 1) / 8
ELSE (a.number * 256 + b.number) / 8
END + 1 AS r,
POWER(2,
CASE (a.number * 256 + b.number) % 8
WHEN 0 THEN 8
ELSE (a.number * 256 + b.number) % 8
END - 1
) AS a18
FROM
master..spt_values a,
master..spt_values b
WHERE
a.type = 'p'
AND b.type = 'p'
AND a.Number <= 256
AND b.Number <= 256
AND (a.number * 256 + b.number) BETWEEN 1 AND 32767
) x
WHERE (SUBSTRING(COLUMNS_UPDATED(), r, 1) & a18) = a18
)
END
go
UPDATE xyz
SET Col4 = 5
go
DROP TABLE xyz
GO
--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html bID=457
--
dbland07666
Thanks,
Constantin
GoodCoder
Wellnow
Hi Scott,
This is me. Glad to hear from you.
You can sed e-mail to ConDrag@hotmail.com
Lookig forward to hear from you.
Constantin
Dale R