A stored Proc inside a cursor inside a trigger

Question

A stored Proc inside a cursor inside a trigger

Ian_McCann

SSCertifiable

Points: 5163
More actions
January 28, 2009 at 6:59 am

#127694

I have come across a potential issue. A stored proc called from inside a cursor in an update trigger. To help this along the table is the subscriber of a central table in the production database.
Is it posible to calculate the volume / frequency of updates to the base table before it all starts falling apart?

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Put down the cursor and back away from the trigger...

To answer your question, I'm not sure. When I first read the headline it was like watching a train wreck, I had to read more.

It may help to see the code, but I really thing this sounds like something that needs to be fixed before it breaks, it is just waiting to happen. Also, a little more detail behind the process would probably also help, you said this table was a subscriber to another table, so that indicates replication.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 2

That is scary.

Really, I think you should try to figure out some other logic to what ever you are doing. Using a Cursor is a cardinal sin. On top of that it is inside a trigger. That makes it very scary. Sorry

-Roy

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 3

Thanks Lynn

I don't think I explained my needs very well.

My problem is not in fixing this ( I am actually looking forward to working with a tally table and Jeff Moden's article),

it is being granted the time to work on it.

Really I'm looking for some math that demonstrats that a trigger is going to do harm.

At the moment it looks like I am the only person in the company that sees this as an issue.

Oh yes, you are right about replication.

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 4

ian McCann (1/28/2009)
Thanks Lynn
I don't think I explained my needs very well.
My problem is not in fixing this ( I am actually looking forward to working with a tally table and Jeff Moden's article),
it is being granted the time to work on it.
Really I'm looking for some math that demonstrats that a trigger is going to do harm.
At the moment it looks like I am the only person in the company that sees this as an issue.
Oh yes, you are right about replication.

We can certainly do some "math" to show why it might (usually is) bad. But, in order to do that, we need to see the code for the trigger and the code for the called proc.

If you just want some simple "math" to demonstrate why a cursor is worse than set based, I think we can probably come up with that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

noeld SSC Guru Points: 96590 More actions · Answer 5

Unless there are almost no changes this is a recipe for disaster!

* Noel