How can I execute .NET code from a SQL TRIGGER?

Question

We have a website under development that maintains a database. This website is replacing classic ASP pages and FoxPro applications along with new development written in ASP.NET 3.5 (mostly VB, a little C# and some AJAX). However, there are other systems that update our database.

Because of those systems that do things like updating statuses in our data, we have a few triggers in the database (SQL Server 2005). Usually it's something simple that is handled in SQL but now we have something else.

Our users will be uploading and storing a file (Excel spreadsheet) that will have updates for the database that should only be processed when an external system gives the go-ahead (i.e. when the trigger is fired). The file will be stored as any other 'attachment' (our database has pointers to files in particular directories) since it needs to be verified up front (long before it would actually be processed).

What we want to do is execute the same method that 'verifies' the file (except this time it will actually process the updates) when the SQL trigger fires.

Is there a relatively uncomplicated way of doing this?

Solution 1

It's been done before you have to use the CLR Stored Procdure...

See here for an example http://www.codeproject.com/KB/database/Managed_Code_in_SQL.aspx

Microsoft also has some CLR examples in there SQL Server Database Engine Samples on codeplex.

Good luck getting DBA sign off.

Another option is to use the xp_cmdshell and execute a "command" on the naitive system, which could then call your .net code. Yet again.. good luck with DBAS ;)

Other than those two I can't think of any other "simple" ways to do it. There are tons of complicated ways to do it..

Solution 2

Without enabling CLR on your SQL Server you can't run SQL CLR stored procs and triggers on it.

Meanwhile SQL CLR trigger is the best solution for your task