In a SQLite database is it better to use tirggers to handle cascading table changes, or is it better to do it programmatically?

Background

I have a couple of projects that use a SQLite DB for data. The data stored in the databases are obviously stored across several tables, linked by key/foreign key values.

The thing is that in these databases, if something changes to one record I have to update several other tables. The best example off the top of my head is deleting a record. I have to make sure all other records related to the one being deleted are deleted as well. Now, this example can be solved using key/foreign key values, I believe, but what about more complicated updates?

Now I'm no pro DB admin, but I know that there needs to be data integrity in the DB or things get ugly.

The Question

So, my question. I know that I have greater control when updating related tables programmatically, but at the cost of human error a开发者_StackOverflow中文版nd time. I may miss something or not implement the tables updates correctly and it takes a lot longer to code in the updates. On the other hand, I can put in triggers and let the DB handle the updates to other tables, but I then lose a lot of control.

So, which one is better? Is each better in different situations?

On the other hand, I can put in triggers and let the DB handle the updates to other tables, but I then lose a lot of control.

What control do you think you're losing? If data integrity requires that "such-and-such an update here requires additional updates there and there", you're not losing control by coding that in a trigger. You're centralizing control, and delegating it to the dbms, which is the only piece of software that can guarantee every application follows those requirements.

I know that I have greater control when updating related tables programmatically, but at the cost of human error and time. I may miss something or not implement the tables updates correctly and it takes a lot longer to code in the updates.

You're thinking like a programmer, not a database designer. (That's an observation, not a criticism.) Don't think, "I might miss something". That way of thinking really misses the mark.

Instead, when you're tempted to delegate data integrity to application code, think "Every programmer and every new or changed application that hits this database from now until the end of time has to get it perfectly right."

Now, honestly, does that really sound like a good idea to you?

(The last Fortune 500 company I worked in had programs written in at least two dozen different languages hitting their OLTP database.)