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?

Answer 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..

Answer 2

I've done some fuzzy searching in assemblies on the database. It is very tricky. First of all you can not use the .NET 2.0 extentions (V3.0, V3.5, V4.0) depending on the SQL Server Version. Secondly you have to make sure that you only use managed code. No COM-Interops etc.

I would think of a better solution since the triggers should be very fast and processing an excel can be very slow. How about an external process that polls on that table?

Answer 3

I would have the trigger put an entry into a queue for processing and have the queue monitored actively for actions which need to be performed. This could be as simple as a table or as complex as a web service call. But it should be quick.

Typically, you don't want trigger operations to be lengthy or involve higher-level business logic. Triggers should be used to maintain the database in a state so that the database can provide consistent services to all database users (so low-level business logic in triggers is still fine). Once you get to application-level business logic, you really don't want that tightly coupled into your database.

Answer 4

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