How to catch SQL Server trigger in C# code

Question

I have to create an external service for existing database which is working with ERP system.

Edit: service will be running on the same machine where SQL Server is running.

Service has to listen for a trigger on a table with documents.

Scenario:

1. User creates a document
2. Trigger calls service method
3. Method queries the database to get data, create document and send it to external API.

Is it possible to catch trigger like that from a C# Worker Service?

Answer 1

It's technically possible to run arbitrary SQL CLR code in a trigger, and make remote web service or rpc calls to code running in a separate service. But since the trigger runs during the transaction and any code you run can delay or undo the database change, it's not recommended. And probably would not be supported by the ERP system.

So the best pattern here is to have the trigger write a row into a local table, and then have an external process poll that table and do the rest of the work, or to configure Change Data Capture or Change Tracking and have the external program query that.