September 28, 2021 by Kenneth Fisher
One of the easiest ways to collect information about table activity is to add a series of audit columns to the table. The most common set of column consists of four columns.
- When was the row created?
- Who created it?
- When was the row last updated?
- Who last updated it?
You won’t always see all four, sometimes you don’t really care about who created or updated the row. And sometimes all you may care about is when it was last updated. Or when it was created. I’m going to give you a generic view of these four but, feel free to mix and match and add anything you might need that I don’t have here.
First, the columns themselves. I’m creating them with a pretty easy naming format. Create(User/Date) and LastUpdate(User/Date). By keeping the names identical from table to table it makes finding the information far easier. Not to mention writing dynamic code to review the information in bulk.
CREATE TABLE AuditColumns ( Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Col1 varchar(50), CreateUser varchar(50) CONSTRAINT df_CreateUser DEFAULT original_login(), CreateDate datetime CONSTRAINT df_CreateDate DEFAULT getdate(), LastUpdateUser varchar(50) CONSTRAINT df_LastUpdateUser DEFAULT original_login(), LastUpdateDate datetime CONSTRAINT df_LastUpdateDate DEFAULT getdate() ); GO
For the user columns I’m using a varchar(50) because that’s usually plenty. You might need more (or even less) or you could even use sysname. I typically use original_login() because it ignores impersonation. However, depending on how your application connects to the database you may need to use something else. I talk about which user function does what here.
For the date columns I’m using the datetime data type, mostly because I’m lazy and this is kind of old code. If you want to look at other options my friend Randolph West (blog|twitter) has a fantastic series of posts on the date and time data types and functions.
You do want to pay somewhat careful attention to the data types you are using because, for example, the way I have it here adds an additional 116 bytes per row. That can add up pretty quickly.
Now these defaults are only going to be useful for inserts and we want update information. So, as much as I hate to do it we need a trigger..
CREATE TRIGGER upd_AuditColumns ON dbo.AuditColumns AFTER UPDATE AS BEGIN IF UPDATE(LastUpdateUser) AND UPDATE(LastUpdateDate) RETURN UPDATE AuditColumns SET LastUpdateUser = DEFAULT, LastUpdateDate = DEFAULT FROM AuditColumns JOIN Inserted ON AuditColumns.Id = Inserted.Id END; GO
I should probably point out that defaults also don’t work if you specifically include the column in your command (i.e. include a value for it in your insert). I could manage that with this trigger as well but I’m choosing not to. My particular use case is for application inserts/updates and I want to leave an option for manual changes without any extra work.
From top to bottom
- This is an update only trigger because I’m only handling the last update columns.
- The IF UPDATE() statement lets me short circuit the trigger if someone is manually updating the last update columns.
- I’m using the DEFAULT keyword in the update statement to refer back to the defaults for the column. If you were wondering why I had defaults for the last update columns when the defaults are primarily for the inserts this is why. This way the default values themselves are all in one place and if I need to change them they are .. well .. like I said, all in one place.
- I’m joining the inserted system view to the original table using the primary key column(s). This way my trigger will handle multi row updates as well as single row updates. All triggers should be able to handle multi row operations!
As with any time you add columns or database code, make sure that you test your application code carefully to make sure you haven’t introduced any bugs.
Category: Auditing, Microsoft SQL Server, SQLServerPedia Syndication | Tags: Auditing, Microsoft SQL Server, triggers
3 thoughts on “Audit Columns”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
What happens to your audit data when a row is deleted? The answer is that it is destroyed along with the deleted data! If a person has access to a table with the audit data in that table,d oesn’t that mean the same person has access to both the source data and the audit data? Isn’t that illegal? One of the basic accounting principles has always been to separate authority. This is why the guy that verifies the purchase orders is not the same guy that placed the purchase orders.
Okay, that’s the law and the audit principles speaking, but we also have a little problem in that one of the basic principles of data modeling press relational guys is that you do not mix data and metadata in the same table. In short, I think this is really a bad way to do it but it’s quick and easy and good for lazy programmers who don’t really want to set up an audit system.
To be fair, I wouldn’t use something like this if I’m giving the data to auditors. This is more for internal work. If I’m worried about legalities or making sure that all of the data is 100% correct (or as close as I can get) then there are a lot of other options. Audit tables, SQL Audit, etc. I’m working my way through a list of options for a presentation I’m doing. This is just one option. I do appreciate the comment FYI, I’m going to make sure to point out the issues with this method which I’m not sure I would have otherwise.
Some thoughts …
I use a similar system. My “System” columns are first in the table definition. I know the column order doesn’t matter, but it means that all System Columns are first / consistent and I find it annoying when more column definitions are added later and the “System” columns wind up in the middle. For good measure I prefix the system column name with “z” so that anything that sorts the columns into Alpha order will list the System Columns last.
I have one additional System Column “EditNo” which is incremented (by trigger, if not done by user) to use for optimistic locking. On a web form I include that as a HIDDEN form field, and when submitted by the user if it is different to the value in DB then someone else must have edited the record within that time. That is smallint (its only a tie break number) and Trigger resets it to 1 if it approaches 32K
A 3rd party APP that we use also has a Transaction No. That is useful because all table updates, within a single batch, get the same Transaction No. Helpful to tie-together what changed (and coupled with a TRIGGER, which inserts DELETED values into a Audit Table, there is then lots of scope for investigating historical changes.
I like your UPDATE and SET using DEFAULT. Didn’t know what was possible, thanks 🙂