Triggers on source table to identify change and create message in SSB Queue

1

Hi SQL Gurus

I need to capture changes in SQL Source tables.
Design approach is - put triggers on all of them and any change, create a corresponding message in SSB Queue.
Further application is polling this queue to process the message and fire a SQL Stored proc to get the underlying table data.

Issue is say table has 50 columns that falls in various sections of a CUSTOMER (say) I just need to process those details that changed rather than complete row. Is there a way of knowing what was the change exactly?

Regards

2

In Triggers you will have Magic Tables (Inserted and Deleted). Inserted will have new values and deleted will have old values on update. By joining these tables you can get what you required.

SELECT case when ISNULL(i.col1, '') <> ISNULL(d.col1, '') then i.col1 end AS new_col1
FROM inserted i
INNER JOIN deleted d
ON i.pkid = d.pkid