I have a console application (in C#) to read data from SQL server (Microsoft SQL 2005) and write data to SQL server. What I need right now is to add a trigger to a table to exec the console application when any row of data is changed.
Not sure what SP available on Microsoft SQL server (2005) to launch a console application? and how can I pass the result from app back? Will it be run as sync or asych manor? Is there any permission issue I have to configure?
The xp_cmdshell stored procedure can be used to launch an external process but this is often disabled for security reasons.
As the console application is already written in C#, perhaps it could be re-written as an SQLCLR stored procedure?
This seems a little shaky to me. Is there any chance of the trigger being called frequently, leading to many launches of the application? Also, I don't think the trigger will complete until the console application has finished or failed. Meantime, the operation that caused the trigger to fire will still be waiting.
Does the application need to run right away? If not, then maybe you could run this as a SQL Agent job periodically.
Trigger slow things down and in your case, this will lead to mass chaos. Triggers is not the route you want to take here.
You might want to consider having a console app that polls the database and every time it finds changes, it will display the changed rows for the users consumption.
You can track these changes using a field like [LastUpdatedDateTime] with a default of GetDate(), and don't send this value in your query. Therefore it will always have the latest timestamp of change. You can alternatively, have an audit table that gets filled by a trigger.
Don't launch external processes from a trigger, you'll bring the server onto its knees. Doesn't matter if is xp_cmdshell or CLR procedure. Instead use Service Broker and issue a SEND in your trigger to queue a message to a local service and rely on activation to do the external dependent processing, asynchronously and on a separate context.
I agree with Ken, you might want to think about changing the architecture. Is the console application reading and writing data to the same SQL server that is invoking it? If so you are better off coding that logic into the trigger or stored procedure itself, and/or changing your database schema to make it so your logic doesn't have to be so complicated.