Recently, I needed to write the same functionality for SQL Server, and as it turns out, you can achieve the same result in SQL Server, but the approach is quite a bit different. So I think it is good to blog about how to do this, so if anyone else has this need, they can see my approach and either use it straight away or adapt it to their needs.
Lets briefly summarize the goals before we get started so it is clear what we are trying to accomplish. The CreatedDate column should reflect the date and time when the row was first inserted into the table. The CreatedDate column should never be able to change. That is, we want to prevent anyone from updating the value in this column, either accidentally or for some malicious reason. The LastModifiedDate column reflects the last time the row was changed. Once a row is updated, the LastModifiedDate value is the time that update occured.
We want to populate both of these columns automatically from the current database time. We don't want to rely on user supplied values for these columns.
This is for two reasons: It is easier if the user doesn't have to worry about supplying values for these administrative type columns so they can just focus on the business data We want to make sure the values are correct.
Using the database value, we know the value is correct and consistent. So first of all, lets define our target table. For this example, I am going to use a table that captures restaurant information.
This table is simplified for our purposes, but will work just fine to demonstrate what we need to. So here is the table. So even though these columns accept NULL values, we'll take care of that in the triggers that follow.
OK, lets take a look at the triggers that can accomplish this. So basically what this does is get the set of affected rows, and go back to the table and update the times to the times that we want. This is necessary to prevent this trigger firing as a nested trigger.
What this does is keeps someone from updating the value of CreatedDate. As we'll show below, even if someone tries to update the value of CreatedDate, it doesn't matter, because our trigger will insure we retain the existing value in our table.
Testing It All Out Lets test everything out and make sure it is working. At this point, I have created the table above and both of my triggers. So lets insert a few rows into the table. However, as I am executing this statement, it is most certainly not January 1st, nor January 1st, This is exactly the type of situation we are designing for, to make sure we always have correct values in these columns and not allow them to be overridden by user supplied values like this that may potentially be incorrect.
So lets see what is on our table at this point. This is exactly what we want. Even though we did not supply values in the first two INSERT statements, our trigger has automatically populated these columns for use with the correct value.
And for the third statement when incorrect values were supplied, these values were not used but again, we have the correct values in these two columns.
We see here that for "House of Pho", the LastModifiedDate has been automatically updated by our trigger to the appropriate time for when the row was changed. The CreatedDate record was left unchanged, as were the records for the other rows in the table. So this is exactly what we were looking for. Finally, lets test the scenario where someone tries to run an UPDATE statement that will modify the CreatedDate column, which as we have discussed, we do not want to allow.
So to test this out, we'll run the following statement: However, with our trigger, we don't allow this to happen and we just retain the existing value. Here is the data: We see here that row 2 for "Ginger Rootz" has a new value for LastModifiedDate and this value is correct. Why All of This Matters What we are essentially trying to do is establish an audit trail for our data. We want to know when a row came into existence and when it was last changed.
For this audit trail to be effective, we need to make sure that these columns always contain the correct values. We don't want to burden a developer or someone writing a SQL statement to worry about how to populate these columns, because we can do that automatically.
At the same time though, we want to prevent these columns from being changed, whether maliciously or accidentally so we are confident the values in these columns are always correct. Using these two triggers, we accomplish both of those goals. This solves half of the problem, in that a developer no longer has to worry about providing a value. But it does not address the problem of preventing the column to be modified. And I feel this is an equally important aspect to solve.
Lately, I've been involved in researching a number of security related items. I am not able to give away many details beyond saying I was involved in researching some issues, but what I can say is that two of the questions that always come up are "when did this row come into existence?
Having audit columns that you know are correct and you know cannot be tampered with is critical when researching these sorts of issues. I have to admit, I myself am sometimes lazy about including columns like CreatedDate and LastModifiedDate in my tables.
I need to be better about that, and about making sure these columns are always properly updated. With these triggers, now I don't have an excuse any more.