Reason for avoiding trigger even though it is scalable with service broker , the source table is in the one database which is designed by other parties . we don't want to make changes by adding trigger to that database.. tat is one of the reason.

client ask us to work this scenario with both notification service and service broker..

The job of the notification service is to notify by calling stored procedure when any inserts happens in that table.

stored procedure will place the message in the service broker queue by opening up a dialog session. the activated queue stored procedure will receive the message body from queue and do the business logic..

another doubt in the asynchronous trigger , say there is a bulk insertion happening to the table ,therefore the service broker will open a new dialog session for each insertion to the table .

therefore it open up a multiple instances of stored procedure , i have doubt in this scenario , is the stored procedure execute in parallel fashion.

i need that to be executed one by one ... that is sequential..

can we do with max_queue_readers or some other one?

can any one clarify it......