October 10, 2002 at 4:38 am
there is a very nice feature in SQL which allows you to get information in a trigger which columns have been updated, and i read a very interesting article by Andy Warren about this too, which helped my somewhat on the way of using this, but there are some thins that you need to think about before you use COLUMNS_UPDATED() functions, the fisrt thing is that you need to think of many columns in a table you have, if you have 8 or less then there is no problem but if you are using more than 8 columns you will need to alter the code a little bit, and if there are more than 32 bolumns you might have problems using it.
Well how do you use COLUMNS_UPDATED(), if you have 8 or less columns you can check if a specific column is updated by
if ( (columns_updated() & column_nuber) = power( 2, ( column_number - 1 ) ) ) where the column_number is the order in the table where the column you want to check for comes. But if you want to check if a specific column has been changed when a table has more than 8 columns you need to us this with the substring function, the first solution is built on a bitmask where every column is represented by a bit, but when its more than 8 columns there are represented as a byte, so lets say that you have 28columns then you need to divide the columns into 4 groups of 8, 8, 8, 4 columns where each group of 8 is one byte and then you can access the bitmap in the group, so how do yo udo this? well...
if((substring(columns_updated(),1,1)&column_number)=power(2, (column_number-1)) )
if((substring(columns_updated(),2,1)&column_number)=power(2, (column_number-1)) )
if((substring(columns_updated(),3,1)&column_number)=power(2, (column_number-1)) )
if((substring(columns_updated(),4,1)&column_number)=power(2, (column_number-1)) )
well i hope you find this information usefull and that you dont have to search the web for information that is difficult to find if you have the same problem as me when i had more than 8 columns
Best Regards
Jonny
to read....
http://www.swynk.com/friends/warren/triggerscolumnsupdated.asp
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q232195&
October 10, 2002 at 4:42 am
Hey Jonny, please post this as a FAQ for future reference, or if you have time to ellaborate on do an Article. This information will be quite usefull and may be lost if only posted here in the forums.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply