The Stupid Problem

You are working on a database that you are not very familiar with as a developer – meaning that you are not going to think of every little thing that a DBA would think of right off the bat. You write a sproc that is archiving data from one table to another table and the last step is to delete the original data.

You issue the DELETE command and your data doesn’t delete! “WTF Just happened here?”

The stupid fix to the stupid problem

I am not saying that this solution is the fix all to this problem, but it might help anyone having a similar problem as I did. I was working on archiving over 3 years of data from a database and I encountered the situation that I described above. I was deleting child elements from a parent table and by the time the process was finished it was as if the child elements were never deleted!

What had happened was…

The players involved here are a parent table and some child tables. For argument’s sake let’s just use a relate-able example. Let’s say I have a dbo.Product table and then a dbo.ProductAudit table. These two tables have a loose or soft relationship that is implied, meaning there is no Foreign key relationship. They key they are using is ProductId. As part of the archive process it is always a good idea to archive tables according to their relationships in revers to avoid dealing with table constraints. Therefore the dbo.ProductAudit table is archived first then the dbo.Product table is last. However here is what happened:

1. Rows archived from dbo.ProductAudit to a complimentary table named archive.ProductAudit
2. Original rows deleted from dbo.ProductAudit
3. Rows archived from dbo.Product to a complimentary table named archive.Product
4. Original rows deleted from dbo.Product

When performing a reconciliation check to make sure that all rows were correctly archived and purged from the original source tables I found that none of the rows I thought I had just deleted from dbo.ProductAudit were actually deleted. This raised my eyebrows to the point where it hurt. Then after performing some deep digging I found the problem finally…

The culprit is a 7 lettered dirty word that gives DBAs nightmares for a good reason

Well, I didn’t realize that there were triggers on the dbo.Product table specifically in the situation where rows were being deleted. Those exact same rows were being inserted right back into dbo.ProductAudit. Thus causing a circular relationship of sorts. I would delete from the Audit table just to have those rows inserted again. This made it appear as though the rows would not delete when in fact they had been deleted. The gotcha here being that a trigger was reinserting those exact same rows that had just been deleted.

Query for looking up triggers on your tables

I put a query like this one together to help me narrow down the culprits and started carefully disabling and re-enabling triggers as needed between statements.

SELECT
	 TargetTable = t.[name]
	,g.*
FROM sys.tables t
	INNER JOIN sys.schemas s
		ON t.schema_id = s.schema_id
			AND s.name = 'dbo'
	INNER JOIN sys.triggers g
		ON g.parent_id = t.object_id
--WHERE g.name LIKE 'trfs%' -- You might want to avoid specific triggers, such as replication triggers
ORDER BY g.name

Enabling and disabling triggers

The following statements can be used for enabling and disabling triggers as needed. Just make absolutely sure you put in a fail safe to re-enable the triggers after execution. For example use a TRY/CATCH block and place the ENABLE statement after the catch to always make sure it is executed since there is no such thing as FINALLY in tSQL.

-- Disable your trigger
ALTER TABLE dbo.YourTable DISABLE TRIGGER targetTriggerName

-- Enable your trigger
ALTER TABLE dbo.YourTable ENABLE TRIGGER targetTriggerName

Conclusion

I have always heard of DBAs bad mouthing the usage of triggers, especially when a developer says “Let’s just use a trigger” and they make a slant face :/ to denote their disapproval. Now I get it. I was actually doing a lot of this my self (>_<) to the point where I wanted to flip a #@(*$#&* table (ノಠ益ಠ)ノ彡┻━┻

I wasted too much time trying to figure this out, just wasn’t obvious because all of my SQL was correct so I was totally thrown off. Well now I know better.