Can I call a C# function by a SQL Server trigger?

Question

An external application come to my database for inserting row in a Sql server table.

I have a web application on my own, and I want on each insert in this table, do some treatement server side.

My idea is to create a trigger on insert on the table, and then call appropriate function.

What is the best way to do this ?

I use framework 3.5 with LINQ to SQL, and a SQL Server 2005 database.

Edit : Thank you for the answers. SQL Server CLR integration doesn't do it. The few libraries supported doesn't meet me requirement.

The table I have to log will take a new record every 5 minutes perhaps, not so much. Maybe I can have a job listening at the table every minute, look at the ID, take all the new ID, do my treatement. Is my solution not too ugly ?

Answer 1

Use SQL Server CLR integration.

Answer 2

If your function is going to take a long time to run, or going to access resources which aren't part of the same database, you might want to consider decoupling the function call from the trigger (so that the original statement that caused the trigger to fire can complete).

In that case, you might want to look at Service Broker, or just use a separate table to queue the requests to call the function (and use a SQL Agent job to dequeue these requests and call the function).