use tempdb;
go
create table t ( 
    Col01 int , Col02 int , Col03 int , Col04 int ,
    Col05 int , Col06 int , Col07 int , Col08 int ,
    Col09 int , Col10 int , Col11 int , Col12 int ,
    Col13 int , Col14 int , Col15 int , Col16 int ,
    Col17 int
);
insert into t values (
    1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17
);
go
create trigger ut on t for update
as
-- General formula:
/*
        Each byte of columns_updated() encodes values for 8 columns.
        So byte #1 contains status of 1 - 8 columns , byte #2 contains
        status of 9 - 16 columns & so on.
        The general formula for calculation is:

        set @byte = ( ( @Col - 1 ) / 8 ) + 1
        set @exp = ( @Col - 1 ) % 8
        -- for single column update, use:
        if substring( columns_updated() , @byte , 1 ) & power( 2, @exp ) > 0

        -- for multiple column updates , use:
        if substring( columns_updated() , @byte , 1 ) & @power = @power
*/
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
        print 'Column #4 was updated'

select @Col = 9 , @exp = ( @Col - 1 ) % 8,
       @byte = ( ( @Col - 1 ) / 8 ) + 1 
if substring( columns_updated() , @byte , 1 ) & power( 2, @exp ) > 0
        print 'Column #9 was updated'

select @Col = 15 , @exp = ( @Col - 1 ) % 8,
       @byte = ( ( @Col - 1 ) / 8 ) + 1 
if substring( columns_updated() , @byte , 1 ) & power( 2, @exp ) > 0
        print 'Column #15 was updated'

select @Col = 16 , @exp = ( @Col - 1 ) % 8,
       @byte = ( ( @Col - 1 ) / 8 ) + 1
if substring( columns_updated() , @byte , 1 ) & power( 2, @exp ) > 0
        print 'Column #16 was updated'

select @Col = 17 , @exp = ( @Col - 1 ) % 8,
       @byte = ( ( @Col - 1 ) / 8 ) + 1 
if substring( columns_updated() , @byte , 1 ) & power( 2, @exp ) > 0
        print 'Column #17 was updated'

select @Col = 11 , @exp = ( @Col - 1 ) % 8,
       @byte = ( ( @Col - 1 ) / 8 ) + 1 ,
       @power = power( 2 , @exp )
select @Col = 13 , @exp = ( @Col - 1 ) % 8, 
       @power = @power + power( 2 , @exp )
if substring( columns_updated() , @byte , 1 ) & @power = @power
        print 'Column #11 & #13 was updated'
go
-- Here are some update statements to check the trigger logic with:
-- The PRINT statement output from the trigger is shown below the
-- update statement that modifies the column(s) that we check for.

update t set col04 = col04 * 2;
/*
Column #4 was updated
*/
update t set col08 = col08 * 2;
update t set col09 = col09 * 2;
/*
Column #9 was updated
*/
update t set col12 = col12 * 2;
update t set col15 = col15 * 2;
/*
Column #15 was updated
*/
update t set col16 = col16 * 2;
/*
Column #16 was updated
*/
update t set col17 = col17 * 2;
/*
Column #17 was updated
*/
update t set col11 = col11 * 2;
update t set col11 = col11 * 2 , col13 = col13 * 2;
/*
Column #11 & #13 was updated
*/
go
drop table t;
go

This page was last updated on May 01, 2006 04:28 PM.