sql server - Can SQL CLR triggers do this? Or is there a better way?

Question

I want to write a service (probably in c#) that monitors a database table. When a record is inserted into the table I want the service to grab the newly inserted data, and perform some complex business logic with it (too complex for TSQL).

One option is to have the service periodically check the table to see if new records have been inserted. The problem with doing it that way is that I want the service to know about the inserts as soon as they happen, and I don't want to kill the database performance.

Doing a little research, it seems like maybe writing a CLR trigger could do the job. I could write trigger in c# that fires when an insert occurs, and then send the newly inserted data to a Windows or WCF service.

What do you think, is that a good (or even possible) use of SQL CLR triggers?

Any other ideas on how to accomplish this?

Answer 1

Probably you should de-couple postprocessing from inserting:

In the Insert trigger, add the record's PK into a queue table.

In a separate service, read from the queue table and do your complex operation. When finished, mark the record as processed (together with error/status info), or delete the record from the queue.