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.