Deleting parent and children

Suppose I want a method to delete a particular record and all its children and grandchildren. E.g., deleting a Customer and all its Invoices and LineItems. Further suppose I have Foreign Keys set up in that SQLite file so that when I manually delete a Customer record, those Invoice and LineItem records are deleted properly.

Is it Best Practice to have such a method just delete that Customer record and assume the Foreign Key constraints in that SQLite file will work? Or do you recommend I error on the side of caution and have the method first delete the grandchildren, then the children, then the parent?

And if you feel it’s fine to just have the method delete the parent, I assume that should be done inside a Transaction because, even though technically that method is only deleting a single record, it will have a cascading effect of deleting multiple records.

Triggers… let the database do the work… so you app doesn’t have to

If I have Foreign Keys set on the grandchildren and the children, why would I need to add Triggers?

FK won’t delete records in another table… it just invalidates the FK link

unless e CASCADING DELETES turned on

Hmmm…I guess I don’t understand. When I set up a Foreign Key on a child record, I am asked what to do ON DELETE and what do ON UPDATE. I choose CASCADE on each of those. And then when I delete the parent, those child records are deleted. Or are you saying the choice as to what to do ON DELETE or what to do ON UPDATE is setting up Triggers without my actually creating individual Triggers manually?

no… you do understand… it was my mind that got fuzzed up today… ON DELETE/ON UPDATE “are” the triggers

Ah. Ok. Then would you wrap such a Delete in a Transaction, even though the method is only deleting a single record? Just in case there’s a database error when the Trigger attempts to delete the children.

We prefer that the app handle the deletion even if there are triggers. I mean, if you control the database 100% of the time that’s doable but I don’t want to depend on a DBA to do their job. So I create a transaction, delete the grandchildren records, the children records, and then the parent records. That way if there’s an error, of any type, I can rollback the transaction.

That’s what I was considering, even though I am 100% in control of the database. I like the idea of rolling back the whole enchilada if there’s an error. But I was wondering if I’m being overly cautious. Thus my original post.

If you control it then you get the error messages. If you let the DB handle it with triggers it’s hard to tell what error messages, if any, you’ll get if a trigger fails. I’m paranoid enough, and gotten bitten by changes to the database enough, to want to handle as much as I can myself without relying on the database, or perhaps more important, the Xojo database plugins to pas on relevant information to you in the event of an error of some kind.