SQL Server: How to get the responsiveness of a trigger, without blocking the transaction that fires the trigger?

Question

On a thread today I posed a desire to get code to execute immediately when a record is inserted rather than resorting to polling.

Triggers are powerful tools, yet dangerous places to put real code, because if the code is slow or blocks, the transaction will block, and it can become a real problem.

For my purpose, polling is not feasible. I really need my code to execute immediately when the record shows up. A windows app is inserting data into the table. I do not have sources to the app and cannot change it. I have full access to the SQL Server.

But it is truly a bad practice to put serious code in the trigger.

So the question beccomes: What is the alternative to a trigger that is as responsive as a trigger? (Or almost as responsive... 1-5 seconds of delay would be OK, but a 30 second polling cycle is really unpleasant. The code to run takes from 0-3 seconds to complete.)

Constraints:

• I only have access to the db, not the app
• SQL Server 2005 and 2008
• Feasible. We aren't looking to invent something crazy, add a lot of infrastructure, or big new systems. We have an app. It talks to SQL Server. Adding more layers prob is not poss.
• Implementing yet another server app (like msg queue server or such) is not feasible for this project. This thing is only going to fire one to fifteen times a day.

Tools at hand (not comprehensive): - .NET - Python - C#

Would like to avoid xp_cmdshell if at all possible.

Answer 1

You might be able to use the service broker, though that is probably overkill, as you state.

Alternately, if you don't want to install/manage an extra service for this one need, you could use xp_cmdshell or a CLR-based trigger to make an external call when needed, to a program that starts the desired process asynchronously (so, outside the transaction that called the trigger) and returns immediately. I would be more wary of this than simply putting some complex code in a trigger though – at least, with a trigger any good SQL Server DBA person would be able to support the code when you are not around rather than requiring some other specific programming experience.

When you say that polling simply isn't an option as an "up to 30 seconds" delay is unacceptable, is there a reason you can't poll more often? If the concern is the load the polling will impart on the server(s) then there are ways to keep the polling very light-weight. No matter what you are going to need the trigger (or something else) to decide if action is needed at the point rows are inserted - if you make this decision in a trigger and simply note it in a simple status table then you could poll every second-or-few to wait for that status marker being updated (a DB hit each second to perform a simple lookup like that once every few seconds (or even every second, or more often) is not going to impart significant load, unless you are using some cloud DB service that charges you by the query). Again, this is far from ideal, but it seems safer to me than the xp_cmdshell option.

Whatever you do, be careful to make sure failures in the process are flagged appropriately. As you are pulling the extra processing out of the main transaction, you can't rely on your existing exception handling to tell you that it failed for some reason – so, you might not know that you have a growing data consistency problem. The key advantage of the trigger here is that by staying in the same transaction you know your data is kept consistent, as one half won't be committed if the other half fails.