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.

Answer 2

What you are describing is sometimes called a Job Queue or a Message Queue. There are several threads about using a DBMS table (as well as other techniques) for doing this that you can find by searching.

I would consider doing anything iike this with a Trigger as being an inappropriate use of a database feature that's easy to get into trouble with anyway. Triggers are best used for low-overhead dbms structural functionality (e.g. fine-grained referential integrity checking) and need to be lightweight and synchronous. It could be done, but probably wouldn't be a good idea.

Answer 3

I would suggest having a trigger on the table that calls the SQL Server Service Broker, that then (asynchronously) executes a CLR stored procedure that does all your work in a different thread.