Run a console application from SQL Server after table update trigger asynchronously?

Question

I am having trouble with finding the best way to solve my issue, please keep in mind I am open to better ways of going about this task.

What I need to do is, after a row's value in my table is updated, I need to use 2 fields from that table as parameters for a console application. Right now I can accomplish this by setting a trigger on the table and then using xp_cmdshell to run the application with the parameters. However I need to do this asynchronously so my stored procedure doesn't hang while it waits for the console application to finish.

Maybe I am going about this the wrong way.

I'm using SQL Server 2008

EDIT - The answer by Andriy M seems to be the best currently but as stated in the comments I need a way to make this happen "Instantly". Is it possible to call a job from a SP or a Trigger? or maybe another way to achieve a similar result?

thanks for the help everyone.

EDIT - I choose he answer below because it helped me the most come to a better solution. What i end up doing was create a job that just queries my table against another which keeps track of updated rows. then when i have the rows i need to update i use xp_cmdshell to run my application with the specified parameters. this solution appears to be working smoothly so far.

Answer 1

There is another disadvantage to running your application directly from the trigger. It has to do with the fact that generally there can be more than one row updated. To account for that in your trigger, you'd probably have to organise a loop over the updated rows and run the application for each individually. Cursors are typically considered as a last resort, and those in a trigger even more so.

In a situation like this I would most probably consider creating a SQL Agent job which would read the updated values from a dedicated table populated by a trigger. The job would still have to use a cursor, I think, but your trigger wouldn't, and the main point is, running the application from the job wouldn't stop your main working process.

Answer 2

In your trigger, put a message onto a Service Broker queue for each row that got updated. Write a stored procedure that processes messages off of the queue. Set the stored procedure as the activation stored procedure for the queue.