I need to call a restful web service with a single parameter from the insert event in a SQL Server database table. I know this is not ideal design but I don't have control of either end point. The usage of this functionality will not exceed 100 events/min at max load.
I'm open to other ideas but the two options I came up with were;
A trigger is run in the context and scope of the statement that causes it to fire - this means, that statement isn't going to complete until the trigger is completely run.
A trigger should be very nimble - small and fast. You should never ever call external services from a trigger, you should not include cursors in triggers, you should not do any heavy lifting or lengthy calculations in a trigger.
If you really must do something like this, I'd recommend an approach that:
see the trigger just put a few values into a "Command" table - those values that the long-running process will need to complete its work
have a de-coupled, separate process (e.g. a stored procedure or something) that will check that "Command" table periodically for new tasks to complete - this can be done in SQL Server using a SQL Server Agent Job
the decoupled process then grabs the information from the "Command" table, does it's work, and updates the database (if necessary)
This way, your trigger is small and nimble and completes quickly, thus not slowing down your main process / main system. The lengthy process is decoupled, standalone, and can be implemented in whichever way makes most sense (stored procedure inside SQL Server, or a separate standalone e.g. command-line tool or whatever makes sense).