CODEWITHSUNDEEP
sqlsql-servert-sqldesign-patterns
fordareh
fordareh

Reputation: 2973

DELETE Trigger vs Stored Procedure to handle removing dependent rows

I've seen various posts about how to implement the deletion of dependent rows from other tables using CASCADE DELETE or various ways of creating or looking up dependencies and creating dynamic SQL.

I'm not crazy about the idea of using CASCADE delete if for no other reason than the overhaead concerns due to the fact that the CASCADE issues so many DELETEs for records that have numerous dependencies which have their own numerous dependencies (not to mention the fact that the results can be hard to track and not all that well-suited to production environments).

So, having resigned myself to writing them in one way or another, I'm wondering what the trade-off is to putting all the necessary deletes into a stored procedure or a delete trigger.

I like the DELETE trigger option, because it keeps the semantics of deletion straight forward. That is:

DELETE FROM [SomeTable] WHERE [SomeColumn] = VAL

Will take care of all the deleting that needs to be taken care of and no single developer can make the mistake of not calling the deletion procedure:

EXECUTE [prc_SomeTable_Delete] VAL

However, I am worried about the use of TRIGGERs since I seem to see a fair number of 'expert' recommendations against their [frequent] use.

From my perspective, the actual implementation of the TRIGGER vs the stored procedure seems nearly identical. So, provided internally we adopt a consistent practice, it seems that the TRIGGER solution should work out just fine.

Is there a best [or most common] practice that should be followed? What should my concerns be in the short term and long term? For the most part, these deletions are going to be issued from a .NET client application - more than likely relying on the Entity Framework for data access/manipulation; how should that affect my decision?

Feel free to post links to exhaustive considerations of the topic as my efforts haven't yet yielded any.

Thanks all.

Upvotes: 1

Views: 1395

Answers (2)

HLGEM
HLGEM

Reputation: 96600

Ther is nothing wrong with appropriate triggers. The people who are against them often have had bad experiences because they were poorly written or because thier developers were too incomptent to think that something could be happening in a trigger.

In this case though, Iw oud not do this task in a trigger. The reason why is the same reason why you don't want to do it using cascading delete. If someone deltes something that has a million child records, you have locked things up.

Deletions do not happen only from an interface, but in this case I prefer the stored proc approach because you can use it to delete records that have an ordinary amount of records and if someone needs to delete millions of records in an adhoc query, then they can write code to delete using batch processing rather than all at once.

Also anyone who is trying to delete manually (for things like dedupping for instance or a the movement of a client's data to another database) will get errors if there are child records which is a good thing in my mind becasue it makes them think about whether the record should be deleted. For instance if you were deleting a bunch of duplicate records, you might not want to delte thier orders but rather move them to a differnt customer (the record you are retaining). Thus if your process hits the FK constraint, you know that your dedupping porcess has an issue and you can fix it. And somtimes those FKs error are justa clue that you don;t want to delete. SO maybe you can delte a customer and his records inteh adress table but if you hit an FK inteh order table, you would not want to delete. It's too easy to make automated deletes like cascade delete and triggers where you forget that sometimess teh FK error is telling you that you should't delete.

Upvotes: 1

marc_s
marc_s

Reputation: 755093

Triggers have the clear drawback that they fire right away, without you being able to postpone their execution. Under heavy use, your system might be so busy executing all those triggers, it'll grind to a halt.

If you can do something like:

  • make a "note" in a table which dependent rows need to be deleted
  • schedule a cleanup task that does its work during off-peak hours, e.g. during the night

your overall system performance should be much better. This however requires that you can wait some time between the time the "parent" row(s) gets deleted, until you actually delete the dependent child rows.

But as you say - the code written is almost the same, so you could definitely use a trigger first and see how it behaves and how it affects your system performance. If it gets too heavy on your system, "de-couple" those events (a parent row being deleted, and the dependent child rows being removed) by using such a "asynchronous" model with a stored proc being called at given intervals to do the cleanup.

Upvotes: 3

Related Questions