SQLite has robust support for triggers. Now, I don't super love triggers. They're really powerful. They can be the perfect solution to many problems, but I find that triggers go into the database and get forgotten. So if you're working on a team, you have to figure out some way to clearly communicate that the triggers exist, what they are, what they're supposed to do, what they do, how to document them.

I find that, the database, in my opinion, can be a bad place for business logic. Now, that being said, they're they are a sharp knife. Triggers are a sharp knife and if you know how to use it, it can be really powerful. And if you don't know how to use it, you'll cut off your finger. So I'm gonna show you one example of triggers.

The docs are very thorough on all of the available triggers. You've got insert, update, delete and then you can have triggers on views as well. I'm just gonna show you one example. If you think that triggers are the answer to your problem, I would encourage you to check out the docs. But for the rest of you, let's look at a single trigger, see how powerful it is and then maybe use it or don't.

So if we do select star from users. I should've put a limit on that. What are you doing? Limit to. So if we do select star from users, we've got these first two people.

I am going to keep track of when someone changes their email. Potentially, that's a sign of, I don't know, fraud or somebody trying to sign up a bunch of free accounts. Who knows? Can't figure out out why I'm gonna do it, but I am gonna do it. And, you know what?

A trigger is the best way to do that. If we select star from email audit, you'll see I have this empty table here that just has ID, the old value and the new value. So this is gonna be our ever growing audit log table that keeps track of all of that. So let's put those guys up there. Then what we're gonna do is we're going to create a trigger and we're gonna call this, let's just call it email updated.

You could come up with a naming scheme if you want. We could say, it's on the users table and it's an update trigger and it's an email. I don't know. I'm just gonna call it email updated because that makes sense to me. And we're gonna say after an update on users.

It really reads like English or like, Apple script from back in the day. So after update on users, begin, and this is where we're gonna put the action. So we're gonna say insert into audits. It's not called audit. Email audit.

And we're going to say user ID old and new with the values of. And then we have access to, this old, schema. Kind of like when we were doing upserts, we had access to the excluded, this magical excluded, keyword or schema. We have access to old. So we're gonna say old dot email.

That was wrong. Actually, that's fine. Old dot ID, that's the user's ID, old dot email and new dot email. So when, when a email is updated on the users table, we're going to insert into the email audit, we're going to insert these values. Now, do you see a problem with this trigger?

All we have done all we have done on this trigger is say after the users table has been updated, which means any value could be updated. So this is a legitimate way to write a trigger. However, you can also scope it down to say only trigger only trigger this, this callback. Basically, only trigger it when a specific column has been updated. So let's do that now.

All we're gonna do is we're gonna add a qualifier here after update of email. So after update of email on users, I cannot get over how how well this reads. Create trigger email updated. After update of email on users begin. Insert into the email audit the old and new columns, the old and new emails end.

So if we create this trigger and then we look at our users and we look at our email audit. Email audit is empty. Users has 2. Let's change this person's email. Instead of Ladarious, we're gonna change it to n to match the first name.

We save that and then we check the email audit and there we go. We've got the user ID, the old email, and the new email automatically inserted into our table. I have to admit, even as someone that doesn't super love triggers, I have to admit that's pretty cool. It's very, very cool. You could use this for an audit log across an entire table, across multiple tables.

There are examples in the docs of how to create an audit log that you can actually put, SQL statements in in here so that if you actually wanted to undo it, you could just run the statement. I think that's kinda cool. Like I said, triggers, very powerful. I would highly recommend reaching for them if your problem is specifically trigger shaped. Otherwise, I would keep most of that specifically trigger shaped.

Otherwise, I would keep most of that logic in the application layer just for discoverability and communication perhaps with other devs on your team. But now that now you know that triggers do exist, they are powerful, and there are insert, update, and delete triggers should you need them.