SQL Trigger + CLR SP vs CLR Trigger

Question

Are there any benefits to call CLR SQL stored procedure from regular trigger rather then deploy CLR trigger straight away?

I need to be notified on specific column change (StatusID) in very large table.

Currently a number of windows services is used. Each monitors its own StatusID, i.e. quires db for specific StatusID: SELECT a,b,c FROM t WHERE StatusID = @status.

I want to try move logic from services to SQL CLR assembly and invoke them using SQL CLR trigger. Is it a good idea? Are there better?

Answer 1

In my opinion, this does not require SQLCLR. However, it depends on what you mean with "being notified". If possible, I'd use a usual trigger and a SQL Agent Job to do the notification.

Answer 2

If you call the services directly from the CLR you'll have to use a standard SOAP binding, because the CLR only supports .NET 2.0 style Web references.

Using the SQL Server Service broker would be a more robust solution, but as you noted it's complicated. The price you will pay for using the SQLCLR is that your update transactions will be rolled back if you have an unhandled error thrown out of your trigger. Also transaction throughput will certainly be affected if you're blocking for a service call with every update.