We have a nice piece of python that sends some emails and interacts with a cloud system. Works fine. But we have to fire it every few minutes to poll the db. We really need, for business purposes, to have the python script fire in real time, so there is no polling delay. (This serves sales people who are on the phone with customers.)
We really do not want a 1 minute polling loop. Or 30 seconds. We want the record to show in the db and for things to happen right away.
The fast way to make this fly is to have it fire when a specific record type is inserted into a table.
Can we fire a python script from a trigger?
Per Aaron's note below, we know that this is a Very Bad Thing™, but this table gets very very little use (0-12 inserts a day). Polling the table fails to meet our business need (we need the .py to run immediately -- it does much more than send an email).
We believe a way to meet our business need is to set up the .net version of python on the SQL Server, and then have T-SQL call the python script the way it calls C# stuff... but we have no idea how to actually do this! (ergo this question).
I asked a follow-up question on Stack Overflow: How do I create a Python CLR procedure in SQL Server?
The question under the question: You have a piece of python. You want it to fire from a SQL trigger, but you know that is a Very Bad Thing. So what do you do to actually accomplish the same effect without having python code in the middle of a SQL operation?
What is the non-trigger, non-polling approach to solving this need?
(The same effect = "insert/update/delete happens in a table and a python script is triggered within 2 seconds of the db event, without polling the table")