Potential Rogue code turning cascade delete on and off PLEASE HELP !

Question

Potential Rogue code turning cascade delete on and off PLEASE HELP !

Simon_L

SSCarpal Tunnel

Points: 4669
More actions
February 27, 2008 at 5:04 am

#120310

Hi
Im using SQL 2000 enterprise. Have a problem on an enviornment where the cascade functionaility of a FK appears to keep getting turned off (and occasionaly back on)
We turn RI off using
SP_MSFOREACHTABLE "ALTER TABLE ? NOCHECK CONSTRAINT ALL"
go
SP_MSFOREACHTABLE "ALTER TABLE ? DISABLE TRIGGER ALL"
go
SP_MSFOREACHTABLE "ALTER TABLE ? CHECK CONSTRAINT ALL"
go
SP_MSFOREACHTABLE "ALTER TABLE ? ENABLE TRIGGER ALL"
go
On a reasonably regular basis but I wouldn't have thought this would effect the cascade functionailty.
Is there any trace I can use, any triggers etc etc to see when this cascade is being turned on and off and by which process
help !
thanks si

Viewing 13 posts - 1 through 12 (of 12 total)

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

Try capturing the events with SQL profiler to see whats happening when this command is issued. BTW why do you disable check constraints as it fails data integrity rules.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 2

turn it off for speed of bulk data inserts (test data)

yes but which events do we trace ?! I want to trace when it is turned off etc by whatever mechanism

~si

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

Use SQL profiler and trace for events on the particular table and the database. You can filter you criteria with SQL profiler.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 4

Sugesh Kumar (2/27/2008)
Use SQL profiler and trace for events on the particular table and the database. You can filter you criteria with SQL profiler.

I wish I could but the powers that be wont allow me to run a trace on the box fulltime as this problem occurs like once a week

many thanks for your reply though 🙂

si

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

Cascade delete is based on the PK/FK relationships (or more specifically - the constraints). Turn them off, and you turn off everything related to them....

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

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 6

and when you turn the relationship in question back on the cascade delete should come back on with it ?

or am I losing the plot 🙂

simon

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

Not 100% sure on that one.... i'd run a test and see which way the wind blows....

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

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 8

Matt Miller (2/27/2008)
Not 100% sure on that one.... i'd run a test and see which way the wind blows....

ok ran

SP_MSFOREACHTABLE "ALTER TABLE ? NOCHECK CONSTRAINT ALL"

go

SP_MSFOREACHTABLE "ALTER TABLE ? DISABLE TRIGGER ALL"

go

SELECT 'FK_mine',

FK = case WHEN OBJECTPROPERTY(object_id('FK_mine'), 'CnstIsDisabled') <> 1

THEN 'ENABLED'

ELSE 'disabled'

end

go

SELECT 'FK_mine',

FK = case WHEN OBJECTPROPERTY(object_id('FK_mine'), 'CnstIsDeleteCascade') = 1

THEN 'CASCADE ON '

ELSE 'CASCADE OFF '

end

go

SP_MSFOREACHTABLE "ALTER TABLE ? CHECK CONSTRAINT ALL"

go

SP_MSFOREACHTABLE "ALTER TABLE ? ENABLE TRIGGER ALL"

go

SELECT 'FK_mine',

FK = case WHEN OBJECTPROPERTY(object_id('FK_mine'), 'CnstIsDisabled') <> 1

THEN 'ENABLED'

ELSE 'disabled'

end

go

SELECT 'FK_mine',

FK = case WHEN OBJECTPROPERTY(object_id('FK_mine'), 'CnstIsDeleteCascade') = 1

THEN 'CASCADE ON '

ELSE 'CASCADE OFF '

end

go

And yes the FK is disabled, then enabled and the delete rule for cascade remains set to 'CASCADE' during both states..

So Im back to square one, something is turning the cascade functionaily on and off on my db for this particular FK ( and its not the way we switch constraints on and off for our data loads )

going to weep

simon

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

The property doesn't change, but the behavior does. Meaning - even if the delete said CASCADE, that's behavior within the constraint, so it doesn't fire (since you've set it not to check)

Just ran this test (note - the "parent" is P1):

select count(*) from p2 --14

ALTER TABLE [dbo].[p2] NOCHECK CONSTRAINT [FK_p2_p1]

delete from p1 where rid=8366 --cascade delete doesn't fire

select count(*) from p2 -- still 14

ALTER TABLE [dbo].[p2] CHECK CONSTRAINT [FK_p2_p1]

delete from p1 where rid=2848 --cascade DOES fire

select count(*) from p2 -- now 13

So - the constraint also drives the cascade.

Of course - with this - I'm now in a bad spot, since I've "screwed up" my DRI. I would have to manually go through and delete orphans created during the time when the constraint is disabled.

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

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 10

Ahhhh perhaps this is my fault for not explaining fully

I fully understand it doesnt fire of the cascade fucntion when set to nocheck even if the FK property still reads CASCADE

My concern was more

How on earth can I track what is turning this the cascade functionility off (but leaving the FK enabled) and then later switching it back on

We had a situation where it was turned off , we turned it back on , and lo and behold it was back off again days later etc

I need to track this somehow and find out whats doing ti

I think my mentioning / being concerned about our global nochecking was possibly a red herring

thanks for your comments so far etc by the way , most kind

Simon

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 11

is this something perhaps where I need something like lumigetn og explorer to look at the trans logs ?

still hitting my head of a brick wall with this

~si

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

You should be able to locate this through profiler. You definitely need to get to the bottom of what's doing that. since it's an ongoing problem - I'm not sure there's any reason to go scan the logs.

Constraints are not right if every time you need to make an update, you need to disable the constraints. Personally - it sounds like the constraints need to be removed, and the onus put on the source systems feeding this DB to "keep their noses clean". As of now - you can pretty much count on your constraints being "worthless" and you having some orphans.

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