This month I’m the lovely host of the T-SQL Tuesday blog party which Adam Machanic (b|t) in December of 2009. The topic this month is Auditing. I chose this topic because auditing, in some form, is needed for all databases (IMO).
Was it the lion, tiger or bear? Oh my!
One of the databases I use to manage had a schema where each table had four fields to keep track of who originally created the record and who last modified the record. The date was also stored for these two events. These four fields were populated by the .Net application that used the table.
We found, over time that this solution had a couple of holes.
1. Values were missing when someone inserted or updated data directly in the database.
2. Values were missing when the .Net programmer forgot to add code to populate the fields.
Luckily, the solution was a quick and easy one.
First, defaults were set on the InsertedBy and InsertedDts fields, SUSER_NAME() and GETDATE() respectively. This provided the user’s account that was used to access the table and the current date.
Second, an update trigger was created to populate ModifiedBy and ModifiedDts. This allowed the application to insert values if they were provided, but default values were available when they weren’t. More importantly, when someone modified the data directly, their login was captured.
CREATE TRIGGER dbo.trg_ClientOrder_iu ON dbo.ClientOrder AFTER UPDATE AS SET NOCOUNT ON UPDATE dbo.ClientOrder SET ModifiedDTS = ISNULL(i.ModifiedDTS, GETDATE()) ,ModifiedBy = ISNULL(i.ModifiedBy, SUSER_NAME()) FROM dbo.ClientOrder AS tb INNER Join inserted AS i ON tb.ClientOrderID = i.ClientOrderID GO
Here you can see that when I inserted the first two records, my login was captured. On the third record I provided values for InsertedBy and InsertedDts.
Next I updated the first record without providing a ModifiedBy or ModifiedDts. On the second row, I did provide a ModifiedBy and ModifiedDts with my update.
This simple solution allows you visibility into who and when data was changed. While this is not a comprehensive solution, it did solve questions that we had over the years.
Thanks for all the fish
Usually this is where I thank the host, but as Spock would say, “That would be self-serving.” So instead I am making a plea to all the amazing female bloggers out there. You know who you are. I want you to ask Adam Machanic (b|t) if you can host the T-SQL Tuesday blog party. Why am I making this plea? I’m glad you asked.
Being a data geek, I went through all the past T-SQL Tuesday invitations, which Steve Jones (b|t) so graciously keeps up to date. (Thanks Steve!) I found that out of the 45 invitations, only FIVE hosts were women. The other amazing fact was that three of us didn’t host until this year!
Ok, I’m getting off my soap box. 🙂