Hey there and thanks for reading. Today I wanted to talk about a few recent additions I made to one of the SQL schemas I run! I was tired of manually adding a LastWriteTime and LastModifiedBy column to each table as they were created in the database, so naturally, I wanted to automate it.
This is a two part process, and it might be a little hacky but it works well enough in development and staging databases. I’ll need to see what the overhead is like in production, but I don’t anticipate any issues.
First up, we need a stored procedure to add 2 columns to a table. Nice and easy:
USE Development; GO ALTER PROCEDURE AddAuditColumns @TableName VARCHAR(MAX) AS BEGIN SET NOCOUNT ON; DECLARE @sql VARCHAR(MAX) BEGIN SET @sql = 'ALTER TABLE ' + @TableName + 'ADD LastWriteTime smalldatetime NOT NULL DEFAULT GETDATE()' -- Change to NOW() for MySQL instead of SQLServer EXEC(@sql) SET @sql = 'ALTER TABLE ' + @TableName + 'ADD LastModifiedBy VARCHAR(60) NULL' -- For purposes of the demo i'll allow null values EXEC(@sql) END END
All of the code in this demo is available on my github page.
Cool, so that’s the easy part, you can execute that by passing any table in your schema to it and it’ll add the columns. LastWriteTime will have a default value of the time of execution or row addition. LastModifiedBy has no default due to how we use it, but you could set it to a value if you so choose.
The next part was a bit more challenging to figure out, I needed to parse event data from the CREATE_TABLE event and determine how to get the name parameter passed to it.
Event data is captured in a trigger by assigning EVENTDATA() to an XML variable. The data comes in a pretty easy to consume XML structure (i’ve formatted this one to make it look pretty, it’s normally displayed without whitespace or linebreaks).
The below event data is captured from the simple line:
CREATE TABLE MyTable (Integer int);
<EVENT_INSTANCE> <EventType>CREATE_TABLE</EventType> <PostTime>2015-09-22T15:40:15.827</PostTime> <SPID>53</SPID> <ServerName>SQLDEV01</ServerName> <LoginName>Domain\Colin</LoginName> <UserName>dbo</UserName> <DatabaseName>Development</DatabaseName> <SchemaName>dbo</SchemaName> <ObjectName>MyTable</ObjectName> <ObjectType>TABLE</ObjectType> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING=" ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText>CREATE TABLE MyTable (Integer int);</CommandText> </TSQLCommand> </EVENT_INSTANCE>
As you can see, we have a lot to work with in this data set. In this example, we’re going to be particularly interested in the <ObjectName> object, since that contains the table name that we’ll want to pass to our stored procedure. Let’s create a trigger to do just that!
Use Development; GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [TRIG_ADD_AUDIT_ON_TABLE_CREATE] ON DATABASE FOR CREATE_TABLE AS BEGIN DECLARE @data XML DECLARE @tablename varchar(max) SET @data = EVENTDATA() SET @tablename = CONVERT(varchar(max), @data.value('(/EVENT_INSTANCE/ObjectName)','nvarchar(100)')) EXEC AddAuditColumns(@tablename) END GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ENABLE TRIGGER [TRIG_ADD_AUDIT_ON_TABLE_CREATE] ON DATABASE GO
That trigger above will snag the EVENTDATA() from the CREATE_TABLE event and add the columns defined in the stored procedure. This is a very extensible way to add columns on create and the best I could come up with on the fly.
Worth noting! If you want to enable this audit creation on the entire schema, you’ll need to modify the Trigger create a little bit, change:
If there’s feedback or issues with the above scripts i’d love to see some pull requests on Github or just leave a comment.