As per my previous post about activating xp_cmdshell for executing command line application in SQLServer there is some additional needs other than just executing, we need to get triggered value such as new value or updated or deleted as argument in command line that we want to execute.
we need do some trick because we cannot directly put the trigger value directly on string that contain command that we want to be ran. so the value will be hold in some variable. for example i want to get the value of new project’s name that will be sent to another application with it’s API through Python Script.
CREATE TRIGGER sendToAPI ON projects
DECLARE @project_name NVARCHAR(MAX)
DECLARE @runcmd VARCHAR(100)
SELECT @project_name = INSERTED.name FROM INSERTED
SET @runcmd = 'C:\Python27\python.exe C:/pyscripts/inputProject.py '+@project_name
EXEC master..xp_cmdshell @runcmd
Happy coding 🙂
Sometime other than run sql command on trigger we need to run external application to be ran, please note there is some security concern related activating this feature (disabled by default) and there is a delay on your application due there is a locking as long as the executing command line application.
So first of all let’s activate executing command line from sqlserver this function by run this commands.
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
- EXEC sp_configure ‘show advanced options’, 1;, Allow advance policy to be changed.
- EXEC sp_configure ‘xp_cmdshell’, 1;, Enabling xp_cmdshell command.
Then you can run command run command xp_cmdshell on your SQLServer, here some example running python script when there is new record on table namely karyawan by creating trigger by name panggil.
CREATE TRIGGER panggil ON karyawan
EXEC master..xp_cmdshell 'C:\Python27\python.exe C:/pyscripts/notify.py';
If you want to delete created trigger below then run this command
DROP TRIGGER panggil;
If you want dive deeply about SQLServer’s Trigger then i recommend this page to start.
Command for installing python module called pip is implicitly included in latest distribution (2.7.9 in my case), Just type following command then pip is ready to use.
$ python -m ensurepip