Call a web service from a trigger
-
Has anyone called a web service from an insert trigger? If so, how did you perform this task?
-
Probabely not impossible to do but it would be considered a worst practice. What action do you need to perform in that webservice that can't be handled on the server?
-
Triggers should be used in short (not lengthy ) jobs. You need to re architecture your solution to minimize Trigger time!!
Also can you post what are you trying to accomplish?
* Noel -
Finally your turn to agree with me
. -
What needs to be accomplished is as new records are added to a database in real time, and address that is submitted needs to be converted to a Lat Long for mapping. I will be using a webservice that converts an address to a Lat Long
-
OK the right approach is that you call the WEB service BEFORE you do the insert! then you don't need the trigger
* Noel -
You could insert id of the new adresses in the trigger and have some job/service check for new adresses every minute and process them there. How do you calculate that exactly?
-
Even simpler... so I'll agree, again
.3999
-
Can't access web service before entry of data. Data is comming from another outside application, and as records are entered into its database replication is being performed to a database that can be used on the extranet. The data in the extranet is were the LAt Long resides.....
-
Yup, sometimes one has to look at the process as a whole and led the process determine the architecture and not the other way around!!
4000 is for that next AGREE
* Noel -
Why can't you insert the data. And after it's in, call the webservice from the application?
[EDIT]
Finally came to the Carpal Tunnel stage... now I can take my vacations
. -
>>... replication is being performed to a database that can be used on the extranet. The data in the extranet is were the LAt Long resides..... <<
If you are trying to replicate then just before the distribution agent starts call an app that can fill out those fileds!
A trigger is not the place to do that. If you still insists have a look at sp_OA* and tryit for your self.
1. IT IS CUMBERSOME
2. IT IS SLOW
3. IT IS HARD TO MAINTAIN
4. IT CAN CREATE MANY PROBLEMS WITH MEMORY MANAGEMENT
just my $0.02
* Noel -
By accessing external ressources out of trigger will hold open that specific transaction until the triggers finishes. If an another transaction will try to insert a new record the table may be still locked so your insert may fail. If this is strictly a mono user environment this might be fine but this is not your case as far as I've read.
So I would also opt for a second run after the data is inserted into your table you will have to select regullary (once per min or so) all records having NULL in the LAT, LON columns and so you can invoke the Web Service...
Bye
Gabor -
You could insert id of the new adresses in the
trigger and have some job/service check for new adresses every minute and process them there.
I second Remi's proposed solution. That sounds like the way to do it.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply