4

Hello and thank you for your time,

I have been searching everywhere online for an example, where a SQL trigger will run an external application, but I have had no luck. All I am seeing is that the EXEC will execute a SQL procedure.

The reason I need this is, I have a SQL Server 2010 with many tables and when an update or insert occurs on certain tables I need my Talend job to run and update the Salesforce tables.

Currently the Talend jobs are running through A task scheduler but the company wants the information to move right away.

Currently this is my code 

CREATE TRIGGER UP_ACCOUNT ON ARCUS
AFTER INSERT, UPDATE
AS 

IF(exists(SELECT IDCUST FROM inserted WHERE IDCUST IS NOT NULL))
    BEGIN 
        EXEC [name_of_application]
    END

I still need to do all the checks to make sure this will not crash anything, but the EXEC statement does not seem to want to execute external commands. Any suggestions would be greatly appreciated.

Thank you

jnoel10
  • 295
  • 2
  • 4
  • 14
  • 2
    I believe this should help you: http://stackoverflow.com/a/1162769/1584772 Pay close attention to everyone's warnings and caveats, though. – dan1111 Oct 19 '12 at 13:04
  • Thank you Dan, I will pay attention to the warnings, this is new to me and I do not want to break our servers :) – jnoel10 Oct 19 '12 at 13:08
  • 1
    You don't want to call it directly from the trigger like this. Both triggers and xp_cmdshell are synchronous so you'd hang-up every session that wrote to this table until it finished. Better off buffering this through a Service Broker queue or else an Agent job. – RBarryYoung Oct 19 '12 at 15:31

1 Answers1

4

Complementing the answer on that post. I would never call another app from sql. Several reasons for that, since security until the fact that someone may move the app and you would need to update your trigger, which means you are responsible for the direct communication between trigger and app.

What I would do is make your trigger write to a table and then have a process that monitors that table (reads and delete rows) and than calls the application. I know you have another player but is safer and easier to maintain.

Mohd
  • 5,523
  • 7
  • 19
  • 30
Diego
  • 34,802
  • 21
  • 91
  • 134
  • Thank you Diego, I agree and after reading what Dan suggested, I am going to create separate tables and just monitor the tables. It makes for more work, but will also make a more secure application – jnoel10 Oct 19 '12 at 13:54