Hi,

Thanks all for the answers. What I found: the updated table has columns NTEX. If the update occurs first in a NTEXT column than the trigger catches the other updated columns.

After executing

updaterequest

setdetails = 'Details updates first and after requestStatus',

requeststatus = 'CLR'

whererequestnum='109413'

the trigger inserts one row in the audit table correctly, a column that counts the updates as 2.

If I run the script inverting the update sequence as below

setrequeststatus = 'CLR',

details = 'requestStatus updates first and after Details',

whererequestnum='109413'

the trigger inserts the row in the audit table with the requestStatus column = 'CLR',

the Details = null and the column that counts the updates as 1.

Here the trigger logic:

...

Declare@update_qualify_for_syncvarchar(5), @pk uniqueidentifier

Select@update_qualify_for_sync='FALSE'

--get the PK

Select @pk = request_id_hex from inserted

--Check the details column(NTEXT): byte number 3 (from left) and column order 2

--Byte 3 columns starts from column number 17. The details is the column number 19, so the position

--inside byte 3 will be 19 - 17 + 1 = 3

Declare@byteOrderInt

Set@byteOrder= 2

If (SUBSTRING(columns_updated(),3,1)= power(2,(@byteOrder-1)))

Begin

Set@update_qualify_for_sync='TRUE'

--set the details as 'UPDATE'

Set@details='UPDATE'

Set@countUpdates=@countUpdates+1

End

Else

Begin

Set@details=null

End

--check request status (I could use here If UPDATE(requeststatus)

Select@new_value=requeststatusfrominserted

Select@old_value=requeststatusfromdeleted

IfNOT(@new_value=@old_value)

Begin

Set@requestStatus=@new_value

Set@update_qualify_for_sync ='TRUE'

Set@countUpdates = @countUpdates+1

End

... code to check other columns

...

--insert the updates into the audit table (PK of this table is autoincrement)

If@update_qualify_for_sync='TRUE'

Begin

insertinto dbo.sync_requests

(request_num,requeststatus,details,updatecount)

values (@pk, @requeststatus,@details,@countUpdates)

End

--end trigger statement

END

I'm using SQL Server 2000 and it does not allow to handle NTEXT columns in the deleted neither inserted tables. This is why I'm using the SUBSTRING with COLUMNS_UPDATE() function.

Also the 2000 version also does not have the VARBINARY(MAX) data types.

Thanks for your help.

Paulo