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 ?

Solution 1

Use SQL Server CLR integration.

Solution 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).

Comments

[leppie]

Not might, but exactly what he needs (unless you cannot call a SQL-CLR function from a trigger).

[leppie]

Thanks for the confirmation :)

[Cyril Gandon]

Thank you for the idea. I try, try and retry, but it is too much pain to use CLR with Linq. A good example for people who want to try it : codeproject.com/KB/database/Managed_Code_in_SQL.aspx

[Cyril Gandon]

Ok, so I find a different approach, but you answer to my question, and I will keep this for an other time !

[Daniel Williams]

This link is no longer valid