Triggers

Video Transcription

Welcome back everyone. In this video, we’re going to start taking a look at another type of stored procedure called triggers. As I mentioned, triggers are a special kind of stored procedure, which is actually attached to an event that happens or occurs in our database. So we typically won’t be actually executing triggers manually, they are automatically executed. Whenever the event occurs. Think like a click event handler when you’re programming a user interface, and Java or Python or a website. So, events that can have triggers attached as defined by the NC SQL standard are before insert, update, or delete, and after insert, update or delete. So, before an insert, update, or delete happens, execute this query first or the stored procedure first, or, or conversely, after an insert, update or delete happens, execute this stored procedure. SQL Server Microsoft SQL Server only supports after from the standard. And then instead of before they do the instead of event. Okay, and I’ll talk about some of the differences here with instead of but before can be duplicated in T SQL by using after and are temporary tables.

So if you remember when we briefly talked about history tables, and you have like the updated and deleted or the inserted and deleted tables after an insert, update or delete happens. So inserts, records that are inserted are temporarily inserted into the inserted table. And records that are updated or deleted are inserted into the deleted table, we can actually pull that information back out using the using the after trigger. But instead of is going to execute the stored procedure instead of doing an insert, update or delete. Okay, so when an insert, update or delete happens, execute this stored procedure instead of doing that action. So that’s just something that SQL Server has kind of decided on as far as functionality goes. But most most SQL Server or most SQL Management, database systems are going to have both before and after. Because those are the standard defined as as ancy are, those are the ones defined by the ancy standard. So triggers in general, are typically going to be executed for when records are inserted and deleted or updated. But they also exist for things like data definition languages for create a table and creating databases. However, those are a little less commonly used. Most of the time, we’re going to work with our insert, update or delete queries, and attaching triggers to those for particular tables. And then with our triggers, we have the inserted and deleted tables available in order for us to track things like you know our history.

So when a record changes, we want to keep track of what the record was previously, or when a record is deleted, or after a record is deleted, we want to take a copy of whatever was deleted and stored into a history table. That is a really common use case for a trigger. But like I mentioned, the DDL triggers do exist as well, they are special types. But these in particular triggers for CREATE TABLE and create database are useful to prevent schema changes. So if you have a very large database, and you don’t want people accidentally creating new tables or messing up your database schema, we can have triggers that can be used to prevent that action from actually happening. But let’s take a look at some examples of these triggers in action. The first thing that I am going to need to do here is actually execute the setup query for part two here. So get everything initialized. Before we just finished our example on stored procedures with output parameters. I’ll take that out and replace that with a trigger Example.

Here is the syntax for creating a trigger. So CREATE TRIGGER. And then my naming scheme is going to be TR for trigger, and then the name of the person name of the table, and then what action it is. So this is a trigger for updates on the person table, right? That is my naming convention there. And so after an update happens, I’m going to insert into a person history, the person ID, first name, middle initial last name and version on from the deleted table. Because when you update, when you run an update, the record that is replaced by the new data is added to the deleted table. So a very useful way too. One way to see how this works here is to just do an insert. So if we run and inserts, we see our four records that get inserted. And then if we scroll down here, we see that our person history table here is empty. Right? That is because our trigger only our stored procedure that we created is triggered only on updates. So after an update happens, not inserts, let’s replace this insert here. If we want to trigger our stored procedure for executing, we need to do an update. So let’s update John Doe to be John Deere. And give us a run. Uh huh. There we are. And you can actually see notice our messages here, we have a bunch of different rows here actually affected starting online 12.

So here is our updates. One row that’s updated. One row that’s inserted into our history table. And then here are four rows from our select for my person table. And then here is the one row from that we select from our person history. So when the update happens, we get the updated row, here is the row that is as a result of the trigger actually executing. But I don’t actually see that as a result of my query here, I don’t have a third table being shown here. Okay, that’s just happening behind the scenes when the trigger actually executes as a result of the update. But we can see our old data. So John Doe, is in our parison history table. But now and our person table, John Deere exists. So this is a very useful way to keep track of history of updates, deletes or inserts into our tables. So triggers in general, fire for the whole set up resulting from each statement. If we run this statement here, so update person, Set version on to the current time, execute this. So here are here is my person table. Here is my person history now. But notice that I have a record now for all of the persons that just changed, right, all the persons that just changed.

So here is so John, John Doe was the original that I that was triggered just a little bit ago. But here are all of my records again, as a result, right. So now here’s John Deere. But all of these have the taint. All of these are going to have very similar timestamps as a result, or at least up here now. So all of these how the exact same timestamp now. And these were the last updated before the update happened right before the update happened. So the triggers fire for the whole set of data that is affected, right, the whole set of data that is affected. So if we go back over here to our messages, you can see the number of rows affected by each one. So here is my original, my original update. And then the rows that are inserted as part of my trigger are the rows that are affected as part of my trigger, and then the rows that I’m pulling back out using my select queries, okay, so original update query, or so original update trigger, and then my to select queries that I just ran to get the results, we can also run our we can also run triggers after inserts and updates. So in particular with this one here, I’m going to run a. So I’m gonna create a trigger after either insert or update.

So we can have a compound trigger. So we can have a procedure that is executed on on after, or after insert, update, or delete, so on and so forth. So we can actually have a stored procedure execute for more than one event. But here, I’m just going to again, insert into sorry, insert into my person history table, from inserted. And so when I inserts, right insert those select star, person in person history. So let’s go ahead and give this a run. Ah, I need to rerun my setup here, let’s let’s erase all of our people that we originally started with. And now excuse this. So here are our, all of our records that we inserted, and there’s a fourth fourth one down here, you just can’t see it off the screen. But nonetheless, here we have a trigger that can now be executed after either insert, or update. So this is a very useful way, like I mentioned, to keep track of your history of things that are being actually inserted, updated or deleted in your database. Alright, so we can also fail and update, which is very useful to do. So let’s check out this structure here. So now I’m creating a trigger, tr IU. So insert update on person. If update, first name and update last name, throw this error both first name and last name can’t be updated. So if we’re trying to both if this is just as a silly example here, but if I’m trying to update both the first name and last name method with the same query, I don’t want to allow that.

So if I try to run this query, ah, here is my error message message 5000 level 16 procedure, ie person line seven, both first name and last name cannot be updated. So that’s my custom error message that I had up here my if statement. So, trying to update both, if I if I looked at our if I looked at my history here you should see all of the original. So John Doe did not get updated to John Deere. And that record here also didn’t change record here also didn’t change, you can no see the the version on is identical to what’s in the history table and what’s in the original. So that means that is still the original record that was inserted into the table to start with. So let’s go ahead and give this a try again. Oh zero. Let’s delete this actually here, I’ve got a little too much going on. But this query actually executes successfully. Because here I’m only changing my last name, and then version on I’m only changing last name and version on not both first and last name at the same time.

So we see here, I last name gets updated. And then if I scroll here on this, you can see the history record that was inserted down here towards the bottom with the new last name for John. But this query also starts to showcase off a our ability to add custom error handling and exception throwing. And in the following video, we’ll take a look a deeper look into this example. But this overall will conclude our general discussion on triggers, which are very useful, very useful things to prevent updates or deletes from happening that you don’t want to allow. So we can actually add some data verification and validation on the database side using triggers These, these can be more complicated than what we can achieve using check constraints. Likewise, triggers are extremely useful for keeping track of data history as data gets inserted, updated or deleted from your database on certain tables as well.