Timestamp a table modification - SQL Server Tutorial

- One common use of an AFTER trigger is to maintain timestamps of data modifications that are made in a table. These can be useful when auditing or other compliance situations arise where you want to know when changes have been made to the data. Let's see how this works by setting up a new table for some customers inside of the KineticoTRG database. In the table, dbo.Customers, we're going to create three different columns. The first one CustomerID will be an integer and be the primary key for the table. Then we have the CustomerName column, it will be an nvarchar 50 data type. And then we have a column called LastModified that'll store a datetime2 data type. Let's highlight lines one through nine, and execute them to create the table in the database. Now we can create a trigger that'll automatically update the last modified date, anytime a new record is added to the table or an existing record is altered. We do that starting on line number 11. Let me go ahead and move this messages window down so that we can see the entire script. So on line 11, we're going to create or alter a trigger called dbo.CustomersModified. If you're using SQL server 2016 or later, you can use this CREATE or ALTER syntax. If you're on something before 2016 though, you can just say, create trigger, dbo.CustomersModified. We're going to attach this trigger to the dbo.Customers table. So we have that on line number 12, we say ON dbo.Customers. And then we need to define when we want this trigger to fire. We want this trigger to fire AFTER any INSERT or UPDATE commands are sent to the customers table. Once we have that on line number 13, we can put the AS key word and inside of here, we have all the different commands that we want to execute anytime an INSERT or UPDATE is added to that customer's table. When we insert or update records, we want to update the dbo.Ccustomers table and set the LastModified column equal to GETDATE. This will fetch the system date and time and put it into the LastModified field. Now we don't want to change the last modified date for every customer in the table. We only want the customers that were updated. So we need to include a WHERE clause in this update command. The way that we do that is by referencing a temporary virtual table that SQL server uses when a trigger is run. The name of the table is inserted. So on line number 17, we have the text FROM inserted. We'll talk more about this useful component, more in the next chapter, but for now, just understand that it's temporarily used to hold a copy of the values that are being updated by the command that fired the trigger in the first place. In the WHERE clause we can then make a join between the CustomerID value in the inserted table and the CustomerID value in our main customers table. This way, we're only updating the last modified date for the customers that are actually being modified or added to the table. Let's highlight lines 11 through 20 and execute them to create the trigger. Now we can test it out with an INSERT statement. I'm going to insert a new row into the customer's table. And the value for CustomerID will be one. And the value for the customer name will be Adam. I'll highlight lines 22 through 26 and execute those to add in the value. And we get the message here that one row is affected, and then it says one row is affected again. We see this twice because this first one corresponds to our original INSERT statement. The second one here that says one row affected again, corresponds to the INSERT or the UPDATE that's being performed by the trigger. Let's go ahead and make sure that the values were inserted into the table correctly. I'm going to run down here and highlight lines 28 to SELECT*FROMdbo.Customers. When I actually get the statement, I see my CustomerID and name here as well as the LastModified date that was put in by the trigger. So we can see that it successfully worked when we did an INSERT. Let's try it again with an UPDATE. I'm going to update that record where the CustomerID is one, to the name Alan, instead of Adam. Pay attention to this LastModified date. Right now it ends in 58.84 seconds. Let's run this update here to change the value. Once again, we see one row is affected twice, and then I'll select everything from dbo.Customers table again. And this time we can see that the name has been changed as well as the LastModified date. Now when you're testing triggers out, it's always a good idea to make sure that you understand how the trigger works when you're sending multiple values at once. Let's scroll down a little bit further, and this time I'm going to insert two records into the table. One for Chondra and one for Madelynn. I'll highlight lines 36 through 39 and execute those. This time we see two rows were affected twice and if I select everything from the table again, by running line 28, we'll see all three values as well as their LastModified date. Now, if you want to clean up this trigger a little bit and remove the duplication of the number of rows that were inserted, we can actually come back up here to the trigger a little bit. And after the AS line on line 14, I'm going to insert a new row and here I can set NOCOUNT ON. This will effectively hide the duplicate message that we're seeing in the messages window every time this trigger is executed. Let's add one more value into the table. I'm going to change this one here on line number 25. Let's go and change this to number four. And we'll insert the name Adam again. I'll highlight to 23 and execute 'em. Oh, and it looks like I forgot to actually run this trigger so let's go ahead and recreate it. I'll highlight lines 11 through 21 and say, execute. That'll update the trigger. Now we can change these values. Let's change this to five and I'll pick a new name. How about Jeff? We'll insert lines 23 through 27. And this time I only see the message once. And that's how you would set up an AFTER INSERT and UPDATE trigger to automatically add useful timestamps to your data tables.