I'm new to SQL Server and I have situation that I want to instantly receive inserted data to my web service url.
So I want to create a trigger in SQL Server to send all inserted data to my web service url.
Can you please help me with this.
-
1I would strongly recommend not doing any (potentially) long running tasks like calling upon external resources in a trigger. The trigger is executed in the context and transaction of the statement that caused it to fire, and that transaction will only complete once the trigger is done. Therefore, a trigger should be very nimble and fast - not do any "heavy lifting" or extensive processing!marc_s– marc_s2018-04-03 04:35:23 +00:00Commented Apr 3, 2018 at 4:35
-
1can we do this ON AFTER INSERT event ??Pawan Developers– Pawan Developers2018-04-03 04:51:41 +00:00Commented Apr 3, 2018 at 4:51
-
No - "ON INSERT" and "AFTER INSERT" are the same thing, reallymarc_s– marc_s2018-04-03 04:53:08 +00:00Commented Apr 3, 2018 at 4:53
-
What exactly do you mean by web service url?MK_– MK_2018-04-03 05:07:44 +00:00Commented Apr 3, 2018 at 5:07
-
@MK_ web service url means any url in my server or you can say API url that will receive data and will process received data.Pawan Developers– Pawan Developers2018-04-03 05:26:48 +00:00Commented Apr 3, 2018 at 5:26
|
Show 1 more comment
1 Answer
Calling a service from trigger is really a bad idea. Even you should avoid calling any service from SQL Server, it should be done from outside the SQL server. You can implement your requirement by following way.
On every insert, using trigger store the newly inserted row inside a table (queue) for processing by some background job. This will make sure that your insert performance will not impact and chances of failure will be minimum.
You can choose one of the below option.
Processing Service : You can create a windows service, which will pool the queue table, it will pick the pending items and will call the required service. After successfully completion it should updated the queue status to processed.
SQL Job : Write a SQL job and schedule it to run after a fixed interval of time like (1 minute). This job will pick the pending items from your queue table and will use the CLR stored procedure
sp_OACreateto call the service.
This will not be real time, but more reliable option with less chances of failure. You can make to near real time by increasing the frequency of job/service.
