Sign up ×
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.

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


share|improve this question

closed as primarily opinion-based by Paul White, RolandoMySQLDBA, swasheck, Kin, Marian Dec 14 '13 at 11:42

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

Out of curiosity, why not make whatever client-side-code that is inserting the "specific record type" run the Python code? Triggers are not the place to do this, as Aaron said. – Max Vernon Dec 13 '13 at 20:31
@AaronBertrand To your suggestion to create a new question: Good call! Done! – samsmith Dec 13 '13 at 20:35
If you are firing the Python script from the trigger, you will be firing it while the transaction is still not committed. The script will not know whether the transaction is going to complete successfully or not. – Andriy M Dec 13 '13 at 21:20
What about having whatever method by which you insert the record handle the notification? Something that receives tx commit confirmation and then emails/interacts with your cloud thingy? Or maybe even Service Broker? Also - I think that these expectations are somewhat unreasonable. – swasheck Dec 13 '13 at 21:42
Can you compile the python code to a CLR? This is where I would start with your question. – jcolebrand Dec 14 '13 at 0:44

1 Answer 1

up vote 10 down vote accepted

Don't make your user transaction wait for the (hopefully!) successful completion of the Python script. Your entire transaction sits there and waits for this external process to run, try to send mail, etc. I doubt the e-mail really has to go out that instant - especially given you can't control any delays it has as it gets routed to the recipient's inbox anyway. Why not just run the process more frequently, if timing is so important?

Please give this tip a look-through.

If you really, really, really want to do this the wrong way, you can just enable xp_cmdshell and fire away.

EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;

Now, assuming the user has access to xp_cmdshell and/or the SQL Server service account can see the folder where the python script is stored, you should be able to do this from within your trigger:

EXEC master..xp_cmdshell N'C:\Python27\python.exe C:\source\';

As an aside, you should state in your question that you aware that this is a very bad thingTM, but you are not concerned with that, for whatever reason. I still don't think you're going to get as real time as you expect, even if you do fire this from the trigger. Have you considered database mail instead of python?

share|improve this answer
Aaron, All your points are valid, but that is my problem. I want to fire python from a trigger, and I have no problem with the issues. (I can have the trigger on the real table push a value into a "job" table, and a trigger on the "job" table run python...) – samsmith Dec 13 '13 at 19:46
Also, your trigger -> other table -> other trigger idea still suffers from the same problem, only now it's worse. The original transaction still has to wait for all of that cascading activity to complete. – Aaron Bertrand Dec 13 '13 at 20:08
good call RE cascading the issue. Won't go there! – samsmith Dec 13 '13 at 20:36

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