Subtitles of the Movie

We have talked about our stored procedure and we've talked about the functions. Remember, both of those actually have to be executed. They are not necessarily automatic; however, of course, as we discuss, they could be added to a scheduled job. In the case of a trigger, it runs automatically. What the trigger is intended to do is to actually make a change to another section of the database based on what you've done. Let's go ahead and go through an example of this so you can see the functionality of the trigger. First, the trigger needs to be run on the table where the data input is going to be taking place. In our example here, I would like every time a new order is placed into the system to have a trigger, an entry in a brand new table that's telling me when the entries have been updated. In this way, I can see if anything is happening. During the course of the day, I could simply see how many new orders there are, for example. Under the table for orders, you will see of course that one of the selections is the trigger. We go inside of the trigger and we simply go to a new trigger. We will see a template for writing the trigger. The trigger is going to be identical to what we did within the actual stored procedure. Now, rather than going into the actual template which has a lot of commenting information, I'm choosing to go over to the Query and show a very simple trigger. In this particular instance, notice that I'm creating a trigger. That trigger is called Update. That trigger will be run on the database object Orders. It's going to run on an insert AS. When I run this insert, I'd like to insert into the table OrderUpdates the OrderID and an OrderDate. We're going to select the maximum OrderID, which means the newest one and then the OrderID is not going to be the OrderID from our table. It's actually utilizing the function called GetDate. It'll get today's date from orders. Our functions are in the fuchsia, whereas our commands are in the blue. Now, if I go back over to the SQL query from the template from the actual new trigger itself, you'll notice that they are including a lot of information to actually encourage you to document these triggers. It's very important. Very minimally, you need to have your authors, your creation dates and your descriptions. This holds true of actual stored procedures, functions and triggers by the way. You'll notice that it also has some commands that'll help us handle the errors. Now, once you actually create this trigger, just execute it. Once you execute it, it's just saved in the background. But remember, nothing is going to happen right now. Notice on my screen, all it basically said is, hey, you've already stored this update trigger in the background. What we're going to do to actually see the trigger work is go into our table. So I'm going to go into the Orders table, which is generating the actual trigger itself. I'm going to go all the way down to the bottom of the data and I'm simply going to add a new record. Now, remember, when you're adding records, you need to understand the structure of the table itself. I know just by looking at CustomerID that that's triggering a foreign key, so it has to be an ID that's already in the system. I know when I go to EmployeeID it's going to be the same case. I must add in something from the system. Then I can go in and put in my dates and fill in the rest of the information. Now I happen to know in this particular example that ShippedDate is not necessary. However, ShipVia is a foreign key. It just was not properly named so it's a little trickier. That should be all I need to actually fill in for this particular data. Remember, of course, when it's working with a new record, it's not until you go the next line that it takes the way of the exclamation mark and in the case of a brand new item, it will put that primary key in once I close out. I have put two new items in it today. One was 11078; the other will be 11079 once I close out. So I'm closing out of my table. Again, we're back to the trigger, which I really didn't need. I should have closed that out and not saved it in the background. At this point now, let's go down to our OrderUpdates. When we open the OrderUpdates to see the information, you will notice now that it set up the two orders that I did today and also correlated to the time the orders were changed. You can see that this is a wonderful feature for seeing how things are modified. If we always like to know when our database has changed, use a trigger such as this and, of course, you can put more entries in. I only chose a brief example of just putting today's date in.