Mickey's T-SQL Ponderings

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!

• Jes Schultz Borland (b|t)
• Erin Stellato (b|t)
• Jen McCown (b|t)
• Wendy Pastrick (b|t)
• And yours truly

Ladies, that is only an 11% representation. Please, please, please email Adam Machanic (b|t) and ask him if you can host. The rules are here.

Ok, I’m getting off my soap box. 🙂