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.