Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
9  
Use a queue -- Service Broker. –  Jon Seigel Dec 13 '13 at 23:12
4  
This is not that hard to do with Service Broker and External Activation. –  RBarryYoung Dec 14 '13 at 2:00
4  
I've reopened this because of the edits. Sam, please don't take 'On Hold' personally: it's nothing to do with politics and everything to do with keeping the site's focus. We've now got a much more useful question as a result, not just useful to you, but to the Googling public at large. –  Jack Douglas Dec 16 '13 at 10:59
    
@JackDouglas Hey, I think it should have stayed on hold. There is very little delta from the OP to now. The whole SE ecosystem is very negative, and puts holds on stuff very aggressively. Big turn off. –  samsmith Dec 16 '13 at 22:13
4  
@samsmith we have a high quality bar precisely because we don't want to become like every forum and Q & A site out there. Sometimes a bit of pushback is necessary to maintain that bar. I think it's worth it. YMMV. –  Aaron Bertrand Dec 17 '13 at 3:16
add comment

1 Answer

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.

share|improve this answer
    
clr trigger.... means SQL Trigger --> CLR trigger --> external script. Interesting! –  samsmith Dec 16 '13 at 22:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.