I'm not sure about the multiple updates, but considering your "synch" process you might consider tightening things up a bit by adding a log table of hashed "profile" values to compare against before your "other" process does its synch step.  This would let you determine when it's appropriate to commit a new data entry to the DirtyData table.

Er...what the heck is he talking about?
Ok, this is what I'm talking about:  When your trigger fires, concatenate all the profile values into one big string and then create a hash value of that string.  Check this hash value against the most recent hash value (if any) associated with the approrpiate userid from your special "hash log" table (a table with previous hashed values and their associated userids).  If the hash matches, don't add anything to the "DirtyData" table (because you've got a duplicate UPDATE).  If it does not match, add the profile data to the DirtyData table and add the userid and hash value to your hash log table.

This will help alleviate your current issue though it won't solve the mystery of the multiple UPDATES.  Beyond that, though, it should prevent you from ever performing unnecessary synchs when a user clicks "UPDATE" without having changed any profile data or when some event causes your table trigger to fire when it does not need to (when no profile info has changed).  It will also inherently provide an audit history showing what profiles are actually changed as opposed to using the general SQL log to show only how many times updates are performed (updated but possibly with identical info and therefore not actually changed). Note that you'd probably want to add a timestamp column if you're interested in audit trails.


Why Bother with a Hash Value?
Unless you want a complete audit trail of what profile data was changed with each UPDATE, there are several decent reasons why a hash value (instead of the actual data) could be considered desirable:

1. You don't have to worry (too much) about protecting the data in the hash table.  This is nice when you're dealing with personal data and have to keep track of where copies of the info get stored.
2. Your hash values won't require a lot of storage space compared to storing copies of all profile field values.
3. Your hash log table schema doesn't have to change if the user profile table schema changes (i.e., if more profile columns are added).
4. It sounds pretty cool when you're explaining the process to your non-technical boss just before annual review time.

OR....
Or you could just have your trigger compare the incoming profile data to the existing profile data and then not perform the update if the incoming matches the existing.  Easy but not nearly as exciting as hash log tables.

Hopefully someone can answer your REAL question regarding the multiple UPDATEs.  All the stuff about hashed values was really just for fun-
-mamlin

Hi All

The problem is resolved using a combination of the new trigger function (newly added in sql 2005) update(), which tells me if the fields I'm interested in have been updated and also comparing the old and new values from the deleted table and the inserted table.  Pseudocode follows ....

-- Was propertyvalue updated ?

IF (UPDATE(PropertyValue))

BEGIN

-- Is data being changed?

-- Get new & old property values
SELECT @PropValueNew = ( SELECT i.PropertyValue FROM INSERTED i )
SELECT @PropValueOld = ( SELECT d.PropertyValue FROM DELETED d )

if (@PropValueNew <> @PropValueOld)

BEGIN -- Data has been changed

Insert into dbo.DirtyData etc etc ......

END

The above may not be perfect but it's fixed the problem and the updates are now being properly processed.

Cheers - Guy