PricklyPear


Hello,

Does anyone have a recommended best practice for how a workflow should "watch for" the event of a SQL (2005) trigger's firing -- rather like the FileWatcher sample

Or better, perhaps a code sample

Thanks in advance for your thoughts.

John



Re: Activity to handle SQL Trigger firing

Dave Britton - VERTIGO


How about using a While activity that looks for some value that the trigger sets in the DB Then you would just need some way to capture your trigger value ...

Dave






Re: Activity to handle SQL Trigger firing

Maurice de Beijer

Using SQL 2005 you can use the SqlDependency class. The best approach would be to create an external service that use the SqlDependency to monitor for database changes and raise an event to the workflow as soon as it has detected one.

Maurice






Re: Activity to handle SQL Trigger firing

Richard Gavel

I've had a similar desire. However, in my case, and I assume anyone elses, SqlDependency is not a good class to monitor inserts because you would not easily know what records got inserted. It's much better for very small tables (like lookup tables) and checking for deletes/updates. My plan is to create service broker event notifications that would contain the necessary information from within a trigger that a custom WF service would listen for and raise workflow events.



Re: Activity to handle SQL Trigger firing

Maurice de Beijer

Hi,

If you want to know about inserts with the SqlDependency you could just monitor a "Select Count(*) From <Table>". This way you get a notification of any insert or delete action.

But then the approach using SQL Service Broker would work just fine :-)

Maurice





Re: Activity to handle SQL Trigger firing

PricklyPear

Hello, Maurice (and thanks also to all who responded) ...

Testing here indicates that the SQL Dependency approach, hooked into a Windows Service, is viable. I'm also looking at going the SQL Service Broker route. When I have some comparative performance info, and have reasonably packaged the code, I'll post it.

In the meantime, if you have code/thoughts, please let me know.

Thanks again,

John






databaseforum