Archive for October, 2011

So you need more information in your trigger?

October 31, 2011 10:11 pm

Lately I’ve been redesigning a lot of older systems. Generally, speaking solutions that were good in sql 2000 are still good in sql 2008, but 2008 has a lot of features that can really help make more elegant solutions. One such feature (introduced in 2005) is Context_Info.

In a nut shell, Context_Info is a 128 byte binary associated with the current session (SPID). On the surface you might be thinking that 128 bytes isn’t a whole lot, and you’d be right. But generally speaking, it should be plenty to hold some basic information about the session that would be uses frequently, or briefly; when going to a table is either too much work to do, or too much work to maintain. In my case, I have a very slim but very large table that performs automatic auditing with an Instead of Insert, Update, Delete trigger. Consider the following table.

CREATE TABLE dbo.Temperature (
	LocationId int,
	TempTime datetime,
	Temperature float NOT NULL,
	PRIMARY KEY (CityId, TempTime)
)

Lets say the table holds temperature information for 500,000 locations and at each the temperature is taken 4 times a day within a 1 hour window. Lastly, the table holds temperate information for the last 10 years. To keep an audit trail on this table you could do a couple things.

1) You could add an extra column just to keep track of who made the last edit, but if that’s all you did you’d have an incomplete audit trail, and would miss any deletes. You’d also add a huge amount of data to this table for less than optimal return.

2) You could create a view that has a dummy “EditedBy” column and do all your edits against the view with a custom trigger. This can be rather cumbersome to implement and just feels like an abuse of the technology. Not to mention if someone comes along and modifies the underlying table directly your audit trail will be compromised.

3) Lastly, you could make a trigger on the table itself that maintains a separate audit table. Something like the following.

CREATE TABLE dbo.TemperatureAudit (
	LocationId int,
	TempTime datetime,
	[Timestamp] datetime,
	ModifiedBy varchar(128),
	ModificationType bit, --1 Insert, NULL Update, 0 Delete
	Temperature float NOT NULL,
	PRIMARY KEY (CityId, TempTime, [Timestamp])
)

All the information in the audit table can be easily gathered in the trigger with nothing too fancy, except ModifiedBy. For that you’ll need Context_Info. Basically, in the trigger you make sure Context_Info is initialized, and if it is, you convert what’s in it to varchar(128) and dump it in your audit table along with everything else. If it isn’t initialized you throw an error and do nothing. With this example your audit trail is only compromised if the trigger is dropped or disabled. If a malicious user can drop or disable a trigger, you’ve got bigger things to worry about than the fact this audit trail is compromised. Here’s some code to show you how easy is it to do some IO with Context_Info.

--Do this part to set up for the trigger
DECLARE @ModifyingUser varchar(128)
	,	@BinVar varbinary(128)
SET @ModifyingUser = 'John.Johnson@example.com'
	
SET @BinVar = CAST(@ModifyingUser AS varbinary(128) )
SET CONTEXT_INFO @BinVar

GO

--This gets the value back out and puts it in another variable. You'd do this in the trigger and just dump the variable into the audit table.
DECLARE @ModifyingUser varchar(128)
SELECT @ModifyingUser=CAST(CONTEXT_INFO() as varchar(128))
SELECT @ModifyingUser

GO

--it's good to clean up after yourself, so you may want to clear out context_info
SET CONTEXT_INFO 0x --clears CONTEXT_INFO
SELECT CAST(CONTEXT_INFO() as varchar(128))

There are a few things I should point out here though. If it’s common to get numerous changes to a single LocationId-TempTime combination then you’ll have to make sure your entries into TemperatureAudit.Timestamp don’t collide. Also, since this is all in the trigger you probably won’t want to take any extra time to check that what’s in Context_Info is valid. You’ll probably just want to take it and run. So a malicious user could fill it with garbage and then perform DELETE FROM Temperature, and it would let that happen even if the ModifiedBy was “InYourFaceFromSpace” or something else equally as useless. That’s why I opted to put Tempurature in the audit table. This has the potential to basically double the space requirement, but it makes the audit trail very robust. For my project, I’m filling Tempurate with the new tempurate during updates and inserts, and the value that was deleted when deleting. That way if a malicious or accidental delete happens the audit table serves as a record of what was there which makes retrieval simple.

Basically, when an insert occurs, the audit table tells you who added what, and when. When an update happens it tells you who updated which to what, and when. And when a deleting, it records who deleted what, and when. Not all solutions demand a solution that is so robust, but for my particular scenario the requirements demanded it.

One final note. You might be thinking, “But Zach, your audit table has LocationId and TempTime, which is the primary key of Temperature. Why not add an arbitrary id to the Temperature table and use that instead to save some space?” Doing so would mean you’d have to jump through some interesting hoops to hide the data in the initial table when deleting. Those hoops would mean a lot more logic every time you select. Plus, to have an arbitrary id that can deal with the same amount of variance as the 2 part primary key I originally laid out, you’d have to use a bigint (16 bytes) which adds up to the same as int (8 bytes) and a datetime (8 bytes). So any space savings you’d gain would really only restrict the growth potential of the table and convoluted the whole scheme. The 2 part primary key embeds meaningful data so any situation that references it becomes more intuitive. Instead of 456187 being the reference for Las Vegas on 2011-10-10 06:00.000 the reference is 732 (Las Vegas’s Id) and 2011-10-10 06:00.000. Why would you represent it in any other way?

zlangner