CODEWITHSUNDEEP
c#sql-serverasp.net-web-apitriggerssqldependency
Jayee
Jayee

Reputation: 562

Best way to get an alert soon after data inserted into a SQL Server table

I have a task, in which I have control over the database but not on the application which does CRUD operations on the SQL Server 2008 R2 database.

Ideally, I would like to call (POST) a web API (please note that, I don't have to wait of the API response) soon after an insert operation on the data table.

Things on my mind to achieve this

  1. Create a SQL Server trigger on the data table (CLR enabled but it's SQL Server 2008R2 so it's using .Net 2.0).

    I may have to create a stored procedure to call web API (POST) (it should be fire and forget, as I don't have to wait for the API response) as part of the trigger.

  2. Service Broker and Dependency injection alert ---> This works well but I am worried about concurrency.

  3. Traditional polling

Questions: If I go with the first solution, does trigger execute in the same transaction scope as the insert operation? If web API is down, will it delay the insert operation? Does this method handle concurrency?

Is there any other method you would recommend? (Please include pros and cons as part of the solution)

Upvotes: 0

Views: 3117

Answers (1)

marc_s
marc_s

Reputation: 754953

The trouble with triggers is the fact that they execute in the context and scope of the statement that caused them to fire, so a delay in the trigger will slow down / block your actual application. Therefore, you should never do lengthy processing or calling external services inside a trigger.

My approach would be:

  1. have a trigger that's very lean and only makes an INSERT into a separate table (a "command" table or whatever you might call it). This table should contain all the necessary, relevant information for the notification

  2. have a separate, scheduled process - either a scheduled SQL Server job, or an external application on a server - that polls that table on a regular basis - every minute, every 15 minutes - whatever you need - and if there's a new entry in the "command" table, it does what it needs to do and sends out notifications or calls external WebAPI services etc.

With this approach, your system is still as responsive as it can be (not delays by "stuck" triggers), and it gives you the flexibility to schedule that "notification sender" application or SQL job as frequently as you need it.

Upvotes: 2

Related Questions