Transcript from the "Calling Functions with Triggers" Lesson
[00:00:00]
>> So triggers, let's say you have a function that you want to run on some sort of event. That you want to as soon as some event happens inside of your database you want some sort of effect to happen afterwards. This might be one of the better reasons to use a function.
[00:00:21] Let's say we needed a audit trail of the names of our recipes. We wanted to see that this used to be called Bob's cake and now it's called Alice's cake and then it's called Eve's cake, right? And we want to have that kind of audit trail of names so that someone could go mouse over it and see what they, this used to be called.
[00:00:37] This would actually be a really prime case of where I would use a function and you would not be able to do this outside of. Well you could do it outside of a trigger you could query the name of what it used to be. Then you could write it and then you could write it to a different table.
[00:00:51] This would actually really easy to write at the trigger, probably I would might lean towards doing that, because having the database handle this make some sense to me. So create a table called updated_recipes with me, there's nothing interesting here, except maybe time stamp. Time stamp is just, it's a time stamp, there's not really too much interesting there.
[00:01:12] If you wanna see what a time stamp looks just write SELECT NOW like this. You'll see, that is what a timestamp looks like in postgres. It's going to keep the id of its own id. It's going to keep the recipe id. You could technically make this a foreign key.
[00:01:32] I'm not going to because I don't want to, but this in all liklihood should be a foreign key. I just want to, I have contrived examples and I didn't want to have to deal with constraints. Old title, new title, time of update and timestamp, okay? Copy and paste that.
[00:01:50] I think originally I did have this as a foreign key. And my examples weren't working and I got sick of it, so, I didn't wanna do it. Anyway, So we're gonna create another function here, nothing here is really that interesting. Other than it returns a trigger, Cuz a trigger is a specific type of function, right.
[00:02:15] And then I did a little bit more programming stuff here. So, IF OLD.title And OLD is a specific name of what it used to be right because it's a trigger it's gonna provide both of those as these like environmental variables. So if OLD.title is not equal to NEW.title, right, so if it changed over the course of this.
[00:02:37] Then we're going to insert this into updated recipe with all the stuff that we asked for here. All this function does. Let's go ahead and copy and paste that to our Postgres. So now I have my function here you can see there I have the set ingredients null, that is procedure.
[00:03:02] This one is a trigger and this one returns character varying. So the key part of this trigger is it's not returning anything because I'm never going to call this trigger Postgres is going to call this trigger right? That's why it has to return a trigger function. And then down here we've created the trigger but nothing the, the trigger is not tied to anything yet.
[00:03:29] So we have to tell it, you're going to react to this event. So that's what this does CREATE OR REPLACE TRIGGER. You have to give it a name. So on an updated recipe trigger that's what I called it. Anytime that the recipe table gets updated, I want this trigger to run.
[00:03:44] If it's going to ask is the old title different than the new title? If it's not it does nothing. If it does get updated then it's going to run insert into this table the updated recipes table. Okay so that's what after update you can say after delete, you could do after any of these other events that exist on recipes.
[00:04:06] If you update 10 rows, I want to run this function once for each row. That's what this for each row execute procedure, log updated recipe name. Okay. So now if I run that, I have created my trigger. So now if I update anything, it's going to, Put stuff in there.
[00:04:30] SELECT * from updated_recipes, you can see I have nothing there. Do I have a query here that, no I have to make one up that's fun. Update, let's see SELECT * from recipes limit 5. Okay, so let's update cookies. So UPDATE recipes SET title equal to My God Cookies.
[00:05:02] I don't know I'm running out of creative juices at this point in the course. WHERE recipe_id =1. So if I do this again. So it's actually not doing that WHERE id = 1 Yeah recipe_id rather. My god cookies. So there we go. So now I should see something, actually have that query in here?
[00:05:58] And now you can see, I didn't insert this the database did it but this is the id of the change. The recipe_id is 1, right? Because I updated cookies that was the recipe_id here. The old title was cookies the new title is My God cookies, hashtag art creative Brian and then the time of the update that happened now so it that's the time that inserted into the database.
[00:06:25] And now we can have this kind of audit trail of what the name of recipes has been over time. So why is this useful to do this way as opposed to doing it in code? Now if anything updates, updated recipes, we will get an audit trail of that no matter what.
[00:06:45] Whether that's someone in PSQL doing it, whether that's my app is doing it, my functions, a different part of the app, a different service. Anything, this trigger will always fire. So this would actually be a pretty compelling use case for a trigger at a function. And even though it is putting some business logic into the database.
[00:07:08] Like you can imagine, let's say I had someone hack my database and get access to my production keys. As long as they didn't have write access to this, we would still get an audit trail of something happening as long as they were not able to drop that table right?
[00:07:23] That's a big if, if you get hacked you are probably screwed anyway. But, it's a thing.
>> Can you explain where old and new came from again? Are those just keywords in SQL?
>> So, this is a trigger right? So as part of the trigger function is, and specifically an update trigger function it provides to you old and new of like, this is what it was, this is what it's going to be.
[00:07:49] You can do stuff based on that. Like, something I could totally do here is I could prevent unwanted changes, right? So I could I could put some sort of like code here that says like, hey if this is changing in such a way don't apply the change keep the old one.
[00:08:07] There's a bunch of stuff you can do I just showed you one that's like creating an audit trail but. Yeah we are scratching the surface here because one I really don't want you to use these that much. And two It's not something I have a ton of experience doing because I really try to limit what kind of work I put into this.
[00:08:27] You cannot run procedures as triggers I think I had that mistake in my last lesson. But that you can only run functions as triggers. Triggers always deal with functions, but there is nothing preventing you from calling a procedure from a function. But I would be upset if you did, probably because at that point, just write the function that does it.
[00:08:51] Don't call a procedure. The more indirection you add to this, the harder it gets.