Problem

I ran into the most puzzling issue recently where I was executing a DELETE statement inside of a transaction among other statements inside of a stored procedure. Then the weirdest most puzzling thing happened, the statement executed, but would not delete rows. It was very bizarre. Then when the whole process was finished if I executed the statement afterwards it worked. I thought maybe it had something to do with executing inside of a transaction, so I took the statement out of the transaction and it still didn’t work. The syntax you are going to see is in SQL Server 2005 format because that is what I had to support, but I was running my tests on SQL Server 2014.
Long story short, after several hours of frustration, I figured it out.

Gotcha

Before I get to the gotcha, I just want to point out that I am a Software Architect with a ton of exposure to tSql, but I am not a DBA so some of these core SQL concentric problems still allude me at times. I always appreciate these moments of absolute clarity due to epiphany, it’s a good feeling. 

Moving along to the gotcha… It turns out that the tables I was working on had triggers and constraints with cascading deletes enabled. This was really causing some seemingly strange behavior during execution. Obviously there is nothing strange about the execution after I learned that these tables had these other objects buzzing away in the background during the execution of my DELETE statements.
I disabled all triggers and constraints (specifically with cascading deletes enabled) associated with the target tables and the strange behavior disappeared and the deletes actually executed. One of the strangest parts about this is that there were no triggers directly on the target tables, so I don’t understand what was blocking the delete.
What was most frustrating about this experience is that I would not see an error, everything just appeared as though it executed.

Delete Example

Everything is always better with an example so here it is. Imagine you have a huge table that needs to be pruned. You are given a list of criteria, but really it boils down to batch deleting rows from a target table.


The amount being deleted is truly arbitrary, so I just chose 5K for the sake of argument. Let’s say this is a special table that has a lot of things happening to it implicitly and indirectly, much like many things in the magic world of SQL engines. Some of these things are constraints and some of these things are triggers, therefore in order to successfully delete those rows above, these constraints and triggers must be temporarily disabled. This of course leads to a new host of problems such as – should you be running this in single user mode and things of that nature. The answer is yes, now how you convince your co-workers, boss and especially your DBA of this is up to you I cannot help you with this. Especially if you have to tear down replication and rebuild it. Have fun with that.

Toggling Constraints

Turns your constraints on and off for a pre-defined set of tables. The sproc following this one really isn’t all that much different except for the syntax of the command being dynamically constructed. Really this is straight-forward.

Toggling Triggers

Turn your triggers on and off. Very much like the previous sproc.

Leave a Reply

Your email address will not be published. Required fields are marked *