August 2, 2005 at 3:59 am
A colleague of mine needs to do this and I suggested this site as a good place to start. Here is his scenario:
Upon a change happening to a record in a particular table in SQL Server 2000, I need the ability to do the following:
1. Add a record to an 'audit' table denoting the table name (where the record was inserted), date-time stamp, primary key and change type.
2. Then pass fields from this record to a web service and wait for a response
3. If the response is 'success', then
flag the record inserted into the table in step 1 with a success status
4. Else
Flag the record inserted into the table in step 1 with a failure status and error description (as returned from the web service)
My question is, what is the best way to send and receive data from/to an external web service upon a trigger firing?
Part 1 is fine - no need to address that - straightforward trigger stuff. Part 2 is the bit that we particularly need help with - passing the inserted data to a web service and then receiving and acting on the response.
Thanks for any help, Phil
August 3, 2005 at 1:25 am
Hi
A few things are easy.
1. building a trigger that inserts data in your table.
2&3. Passing the record to a webservice and wait is something you don't want to do in SQL.
I suggest NOT to use a trigger. Rewrite the process, first send the data to the webservice, wait for the response and then insert the data in your table. THis really speeds up SQL.
JP
August 3, 2005 at 1:43 am
I defniitely agree with JP. Most importantly, you want to:
Keep transactions as short as possible. Start them as late as possible and close them as soon as possible.
August 3, 2005 at 4:26 am
Or if you can't rewrite it, launch a JOB that will do the web service stuff for you asynchronously.
August 3, 2005 at 5:02 pm
Thank you everyone - sensible advice as usual - will pass on and discuss with my colleague.
Phil
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply