Get Trigger Value As Executed Command Line Arguments

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
FOR INSERT
AS
BEGIN
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

end

 

Happy coding 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s