Case of the Mysterious Missing Trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Case of the Mysterious Missing Trigger

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-07-27 : 12:02:28

I ran a command that deleted rows from table A. I discovered that in doing so rows from table B were also deleted.

Some pertinent facts:

1. Table A has no triggers.

2. I dropped or deleted all 3 triggers from table B.

3. Table A primary key is of type uniqueidentifier.

4. Table B has a foreign key matching the primary key of table A.

So what explains the deletion of rows from B when rows are deleted from A, and how do I temporarily suppress that happening?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-07-27 : 12:11:44

what command did you run to delete rows from table A?
is there a clean up sql job?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 12:18:02

table B had a on delete cascade specified on foreign key constraint i think

you need to drop and recreate the fk without cascading option for avoiding this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-07-27 : 13:16:34

Yes, that turned out to be the magical secret. I scripted the foreign key as DROP To and CREATE To, then ran the DROP To script. I assume that running the CREATE To script will set everything back to what it was, as if it had never been dropped.

Thanks.

quote:
Originally posted by visakh16

table B had a on delete cascade specified on foreign key constraint i think

you need to drop and recreate the fk without cascading option for avoiding this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 15:22:26

quote:
Originally posted by BobRoberts

Yes, that turned out to be the magical secret. I scripted the foreign key as DROP To and CREATE To, then ran the DROP To script. I assume that running the CREATE To script will set everything back to what it was, as if it had never been dropped.

Thanks.

quote:
Originally posted by visakh16

table B had a on delete cascade specified on foreign key constraint i think

you need to drop and recreate the fk without cascading option for avoiding this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

make sure you remove ON DELETE clause before you create it again

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-28 : 13:34:37

erm.. Why is it a problem at all?

If the second table had a constraint specified with an ON DELETE CASCADE then this *was the desired behaviour*

If you change this you won't be able to delete from tableA before removing the relevant rows in tableB (because to do so would invalidate the key constraint....)

So are you sure you actually have a problem here?

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources