SQL Server 2000

Question

SQL Server 2000

Viewing 9 posts - 16 through 23 (of 23 total)

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

Jeff Moden SSC Guru Points: 1003938 More actions · Answer 1

Oh yeah... I forgot to mention that, Matt. Thanks for the reminder,

Shifting gears a bit here... What a PITA.... I hate it when they do stuff like that... In 2k, the default was OFF, in 2k5, the default is ON... what bit of ill-conceived flotsum from the shallow end of the gene pool would design or approve such a horrible and potentially dangerous change in defaults? I'm think a diet rich in high-velocity pork would be the second most appropriate gesture to those folks. 😛

--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)

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

Jeff Moden (10/21/2008)
Oh yeah... I forgot to mention that, Matt. Thanks for the reminder,
Shifting gears a bit here... What a PITA.... I hate it when they do stuff like that... In 2k, the default was OFF, in 2k5, the default is ON... what bit of ill-conceived flotsum from the shallow end of the gene pool would design or approve such a horrible and potentially dangerous change in defaults? I'm think a diet rich in high-velocity pork would be the the second most appropriate gesture to those folks. 😛

Not going to disagree with you there.... I'm still trying to figure out who thought recursive triggers were a good idea to begin with.......

----------------------------------------------------------------------------------
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?

Greg Snidow SSCoach Points: 16106 More actions · Answer 3

Matt Miller (10/21/2008)
Just be sure to disable recursive triggers...:)

Now wait a minute...no one ever mentioned that one to me before. I'm not sure exactly what that means. And are you saying there is something about my trigger... such a nice, little, simple trigger...that could potentially have an adverse affect, to the detriment of the whole world?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

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

Greg Snidow (10/24/2008)
Matt Miller (10/21/2008)
Just be sure to disable recursive triggers...:)
Now wait a minute...no one ever mentioned that one to me before. I'm not sure exactly what that means. And are you saying there is something about my trigger... such a nice, little, simple trigger...that could potentially have an adverse affect, to the detriment of the whole world?

No... he's talking about my code example. Since the trigger will do an update no matter which columns are updated, my trigger will cause an "infinite" loop up to the limits for recursion if recursive triggers are enabled. A recursive trigger is a trigger than will cause it's self to be fire if it updates it's own table.

In SQL Server 2000, the default is to NOT allow that to happen.

In SQL Server 2005, the default is to ALLOW that to happen.

--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)

Greg Snidow SSCoach Points: 16106 More actions · Answer 5

Ahh, what relief. I get it, the trigger would fire when it updates the field in question, which would cause it to fire again. Never thought of that. Thanks for the clarification.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Jeff Moden (10/21/2008)
Shifting gears a bit here... What a PITA.... I hate it when they do stuff like that... In 2k, the default was OFF, in 2k5, the default is ON...

Where is that documented? I just created a new DB on 2005 to have a look and recursive triggers is off by default. Nested triggers (the server-wide setting) is on by default so triggers can fire other triggers, but recursive triggers (the DB-level setting) is off, so a trigger can't fire itself.

2008's the same.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003938 More actions · Answer 7

GilaMonster (10/25/2008)
Jeff Moden (10/21/2008)
Shifting gears a bit here... What a PITA.... I hate it when they do stuff like that... In 2k, the default was OFF, in 2k5, the default is ON...
Where is that documented? I just created a new DB on 2005 to have a look and recursive triggers is off by default. Nested triggers (the server-wide setting) is on by default so triggers can fire other triggers, but recursive triggers (the DB-level setting) is off, so a trigger can't fire itself.
2008's the same.

Funny... on mine (Developer's editition, pre-sp1), recursive triggers was set to ON in 2k5 even after I installed sp2. You're correct about the documentation, though... it say's...

An AFTER trigger does not call itself recursively unless the RECURSIVE_TRIGGERS database option is set.

... which would imply that it is not set by default. But, mine was.

--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)

Jeff Moden SSC Guru Points: 1003938 More actions · Answer 8

Greg Snidow (10/25/2008)
Ahh, what relief. I get it, the trigger would fire when it updates the field in question, which would cause it to fire again. Never thought of that. Thanks for the clarification.

No problem. See Gail's post above... her installations DON'T have it set as a default... I don't know why mine was. Bottom line is, check it. 🙂

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Jeff Moden (10/25/2008)
Funny... on mine (Developer's editition, pre-sp1), recursive triggers was set to ON in 2k5 even after I installed sp2.

Check Model. It could be that at some point somehow model got changed and that would likely propagate to all new DBs.

I have 2005 Developer SP2 and 2008 developer RTM

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass