CLR trigger question

Question

CLR trigger question

andy.gray

SSC Journeyman

Points: 98
More actions
July 3, 2008 at 9:28 am

#190054

I'm using a clr "for insert" trigger as I need to do a bit of string manipulation and the trigger opens a datareader to loop through the records in the inserted table.
Under certain circumstances I need to update the newly inserted record, how am I meant to achieve this since firing off an update command on the same (context) connection causes an error "There is already an open DataReader associated with this command which must be closed first".
Is there a recommended strategy for dealing with this type of scenario? As the only way to loop through the inserted table is to use the context connection, am I supposed to open a second connection to perform the update? Maybe I should use an INSTEAD OF INSERT trigger to do what I'm trying to do?
Kind regards
Andy

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Just curious - what made you go for a CLR trigger instead of a T-SQL one? This would be a whole lot more straightforward if the process stayed in T-SQL, and you haven't mentioned anything that would seem to justify going at it "the hard way".

That being said - you probably would need a secondary connection, and a second command, OR you'd need to switch over to a dataadapter (which would allow you to update the row you're looking at).

The INSTEAD OF trigger would probably still run into the same scenario, since you'd have to issue the UPDATE on all rows (and not just the ones you wish to "override").

Still - this is going to get costly and is going to suffer quite a bit perf-wise. Keeping this in CLR is not something I would do lightly (it's MUCH harder than doing something like this in T-SQL), so be sure you can't do it in T-SQL first.......

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

Matt is correct here. Unless you are doing complex regular expression replacements, your operation will likely be slower in CLR versus a set based TSQL implementation. I would venture to say that what you are trying to do can likely be done in a single operation, but we would need to know more about the specifics of what you are trying to accomplish. If you can post your CLR code for the trigger here, it is probable that someone will be able to offer a TSQL implementation that when tested will beat your CLR implementation hands down.

andy.gray SSC Journeyman Points: 98 More actions · Answer 3

Hi guys, thanks for the prompt replies.

I understand your concerns about performance but I do need to do a lot of string manipulation. I'm trying to work around a "feature" in Microsoft Dynamics CRM 4 for one of our customers. CRM has built the body for an html email and I need to "adjust" this html.

From what I've been taught about SQL 2005 & 2008, string manipulation is a lot more performant if you use clr code than TSQL. Some of this string manipulation, I'm not even sure I can do using TSQL!! Benchmarks I was shown during a recent ISV/Partner course showed this quite dramatically which surprised me.

If I get the time, I will see if I can write it using TSQL and compare the two methods. So if I go down the clr route, you'd suggest using a data adapter which does make sense.

Regards

Andy

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

andy.gray (7/3/2008)
Hi guys, thanks for the prompt replies.
I understand your concerns about performance but I do need to do a lot of string manipulation. I'm trying to work around a "feature" in Microsoft Dynamics CRM 4 for one of our customers. CRM has built the body for an html email and I need to "adjust" this html.
From what I've been taught about SQL 2005 & 2008, string manipulation is a lot more performant if you use clr code than TSQL. Some of this string manipulation, I'm not even sure I can do using TSQL!! Benchmarks I was shown during a recent ISV/Partner course showed this quite dramatically which surprised me.
If I get the time, I will see if I can write it using TSQL and compare the two methods. So if I go down the clr route, you'd suggest using a data adapter which does make sense.
Regards
Andy

For what it's worth - I get around that by implementing the Regex (find/replace) functionality in CLR functions, and then do the rest of the logic within T-SQL. By doing it that way - the operation is still a set-based operation on the T-SQL side, and the Regex performance through a CLR function is awesome, so you end up with something that flies.

Meaning - use T-SQL to find and perform the update, with a CLR function providing the actual pattern-matching and replace logic. Moving the data access (the updates, not so much the reads) back into T-SQL will buy you performance like you wouldn't believe.

Also - if you happen to get a chance - dig up some of the articles by Jeff Moden on the "tally table". There are some rather amazing tricks he pulls with those to do a LOT of string manipulation, all the while without trading out a whole lot of performance. Depending on the scenario, those can be faster than even the Regex functionality (depending on whaty kind of pattern you're looking for, how long the string, etc....)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?