4

When cleaning out old data from a set of database tables, is it safer to have the logic in the application or in a database trigger?

I am upgrading an application that I wrote a while back (and in a hurried fashion) and one of the things I want to clean up first is ensuring that data which is no longer being used is propmtly deleted. Currently this is being done in my application itself via a number of SQL calls to various tables. Some of these queries take a bit of time to run and as the application is PHP driven, I don't want users having to wait too long.

I was therefore thinking about creating a database trigger on one table that would merrily clean up all the other tables and then allow the single row delete to occur from my master table.

I am considering these pros and cons at the moment:

  • Database trigger
  • (+) Invisible to the application
  • (+) Simplifies application logic - new objects can be simplified
  • (-) Adds complexity to overall maintenance - have to maintain code and triggers during table changes etc.

  • Application Logic

  • (+) Keeps all maintenance tasks in one place
  • (-) Longer page loads on certain tasks
  • (-) More chance of a request timing out - orphaning data

If you have any further insight, considerations I haven't thought of here, experience with one vs the other or can point me towards some reading on the matter I would love to hear it.

Edit: I originally thought that my pages would run quicker, but if I am creating a Before Delete trigger, will the page loads be the same - as in the application sends a tiny query through but it won't be actioned until the entire set of statements in the trigger complete?

2
  • You are right. In most databases the trigger will be executed before your delete query completes, so your page load still has to wait for the deletion. If you don't want to wait for the delete you should not do it during the page load. Use a recurring batch job as @nonnb says, a queued procedure call, something that happens independently.
    – joshp
    Aug 3, 2012 at 5:24
  • Another + for Application Logic (depending on your application having it of course) is if its easier to invalidate cached data in the application.
    – Kevin Shea
    Dec 20, 2016 at 23:41

3 Answers 3

5

In the case of archiving or deleting old or obsolete data, would you consider a third option, i.e. a scheduled batch job, which detects and deletes old data. The job could then be scheduled e.g. once per day at a relatively quiet time, thus having minimal impact on your application. The job could be part of other standard database jobs, e.g. OPTIMIZE / reindexing etc.

The one exception to this could be if. e.g. you are updating a 2 tables with a 1 : many relationship (e.g. Invoice and InvoiceLineItem), whereby you've decided to delete all existing invoice line items before inserting the new ones. Because the deletion is deterministic (you know exactly what you need to delete), and because you also want the deletion to be part of a larger transaction which includes the subsequent inserts, then I would say that this would be a good candidate for the application tier.

However, I cannot see the purpose of doing this in a trigger at all - this could cause unnecessary performance loss for your inserts / updates while your trigger searches for stale data, and then locks unrelated rows elsewhere after deleting them.

4
  • Good point, I do a lot of my heavy lifting in this app via a schedule, but the users wanted to see data vanish immediately and as the tables are in fact related multiple one to many relationships, I figured a simple trigger might work cleanly.
    – Fluffeh
    Aug 3, 2012 at 7:20
  • @Fluffeh: Can't you just use ON DELETE CASCADE then?
    – tdammers
    Aug 3, 2012 at 8:24
  • @tdammers I haven't enforced keys in the database, like I said, I built it in a very hurried fashion :(
    – Fluffeh
    Aug 5, 2012 at 8:56
  • 1
    @Fluffeh: I still think adding the foreign keys after the fact and enforcing them could be easier. Unless you're using MyISAM, in which case I'd opt for a bunch of cleanup queries and run them manually or from a cron job. Probably more efficient to delete them all in one go every hour or so than on-the-fly, considering indexes and fragmentation and all that.
    – tdammers
    Aug 5, 2012 at 9:38
3

If you have just one application which has write access to your database (or at least to the relevant tables here), and your CRUD operations are in one place of that application, then there is normally no need for using triggers for keeping your data consistent. Triggers are a good thing when you don't know beforehand who and what processes/applications will access your db in the future, and you want to keep a lot of consistency rules in one place, mostly in your DB. So, my "rule of thumb":

  • when you have the only one application which does write access to your DB under your control, put the db logic (like deletion rules) completely in your application, because of easier maintenance
  • when the DB shall keep its data consistent by itself, because you have a growing number of applications developed by bunch of people, and you cannot assure every developer of that team understands all consistency rules of every part of your database, then use triggers

And as you have seen by yourself, performance may or may not influence your decision, but often the differences are negligible, and bottlenecks may be solved different anyhow.

1
  • I'm agree. I had to move all my triggeres to server logic , too much complex, slow and hard to maintain , not really application logic client, but on the server, considering a single page application
    – stackdave
    Mar 3, 2018 at 11:32
1

You can use ON DELETE CASCADE but it will be time-consuming if you have many child records. Many dbas will not allow you to use it as it can lock up the system. Personally, if the users want to see the data gone immediately, I would use an is_deleted flag field on the parent table (with a view that shows only the non-deleted.) and then really delete the records in batches during non-production times.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.