March 17, 2008 at 11:13 am
I am working with a developer who is trying to implementing the equivalent of an asynchronus trigger on a table by having the trigger just send all inserted records to a Service Broker Queue.
This seems to work fine for a single-record insert, but for a multi-record insert, there were "problems". When I explained to him that SQL triggers fire once per statement, NOT once per record he understood and then implemented the trigger with a Cursor that loops through inserted and SEND's each inserted record to the queue. Well that works, but i would really rather not be using a Cursor in a trigger on a heavily used table.
Is there any reasonable way around this? As far as I can tell the service broker SEND command is single-record only. Is there any way to do the equivalent of a multi-record SEND, just as we could do a multi-record INSERT?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 7, 2008 at 8:06 am
In the trigger you could put all the data from the INSERTED virtual table into a single XML instance and send that to the queue. Of course, you'd have to parse the XML at the "other side" to extract individual rows.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
April 8, 2008 at 11:45 pm
Yeah, that's the conclusion that I came to also. I probably will load all the records into XML in the trigger and then cursor them out in an intermediate Service routine.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply