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

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 2

I have a service that polls the database every minute, it doesn't cause that much performance problems and it is a clean solution. Plus if your service or other wcf endpoint is not there your trigger will fail or be lost and you will have to poll anyways later.

Answer 3

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 4

I would not recommend using a CLR trigger, or any sort of trigger for this. You are opening yourself to having serious maintainability and potential locking issues. (A very simple trigger that chucks stuff into an audit/queue table may be acceptable IF you don't care about @@identity after inserts and you will never lock the audit/queue table up)

Instead, from your application/orm you should trigger inserting stuff into a queue table and have this queue processed on a regular basis. This can be done by having a transaction in your ORM or kicking off a stored proc the starts a transaction commits the change and audit/queue atomically. (be careful with locking here)

If you need immediate action, look at spawning a job to clear the queue after you do a insert/update/delete on the table and

Also ensure you are double checking the queue once a minute or so in case the background process was not kicked off properly. If its a web app and you want to avoid spawning threads you could communicate with a background process to clear up the queue.

Answer 5

Why not implement the insert in a stored procedure, and do the business logic in the procedure after the insert? What is so complicated about it that it can't be written in T-SQL?

Answer 6

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.