SQL Service Broker to notify external app

问题:

After much searching on Service Broker, I came across this question and answer:

My scenario is similar to the one mentioned there, in that I have a SQL trigger on a table, and in this trigger I'm calling xp_cmdshell to pass primary key information to an external exe.

I can see the value of setting up a Service Broker queue and service instead of using a trigger on a table. As I understand it, the activation sproc that I set up to process messages in the queue would now contain my call to xp_cmdshell.

However, I can't figure out how to hook up a "change to data in a table" event as the triggering event. I would expect the CREATE EVENT NOTIFICATION clause to have an ON TABLE [x] FOR [INSERT|UPDATE] part to it, but it does not.

Any pointers would be appreciated.

回答1:

The link you provided actually gives a great answer to your question. As described here, event notification feature is really meant for DDL and database state changes. What you want is probably to stick with triggers, but make the xp_cmdshell call asynchronous. I really don't think any better answer can be provided than the one Remus gave to the linked question.