.Net Meanderings

In my opinion, one of the neatest features of having the CLR hosted by Yukon is that we can use managed code to bridge out to the non-database world. So we can take non-database data and present it as though it were a result set - even up to the point where we can issue selects and joins against this non database data source.

Another cool feature is that in Yukon we can now hook triggers on to DDL as well as DML constructs (so things like CREATE TABLE and CREATE USER as well as INSERT and DELETE can fire triggers). Yet another neat thing is that we can create triggers in managed code. When we glue these three new features together we get the ability to provide quite powerful auditing functionality - imagine having the ability to create an Event Log record every time a new user was added to a database.

Now one issue with this is how do we know what happened when a DDL trigger fires - after all we won't get any entries in the INSERTED and DELETED tables. So somehow the database engine needs to deliver the trigger reason (or context) to the trigger. It does this by making the data accessible in the form of an XML document. Here's an example of what the data would look like for a CREATE USER statement:

<?xml version="1.0" encoding="utf-16"?>
<EVENT_INSTANCE>
  <PostTime>2004-03-09T20:04:47.450</PostTime>
  <SPID>53</SPID>
  <EventType>CREATE_USER</EventType>
  <Database>YukonTest</Database>
  <Object>bert</Object>
  <ObjectType>USER</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" 
                ANSI_NULL_DEFAULT="ON" 
                ANSI_PADDING="ON" 
                QUOTED_IDENTIFIER="ON" 
                ENCRYPTED="FALSE" />
    <CommandText>create user bert</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

So the data we would be interested in is in this XML document. Specifically in this example I'll extract the new user name from the Object element and the database name from the Database element.

Here;s the C# code:

public static void AddUser ()
{
  SqlTriggerContext ctx = SqlContext.GetTriggerContext();

  if (ctx.TriggerAction == TriggerAction.CreateUser)
  {
    string s = new string(ctx.EventData.Value);

    StringReader r = new StringReader(s);
    XmlReader reader = new XmlTextReader(r);
    XmlDocument doc = new XmlDocument();
    doc.Load(reader);

    reader.Dispose();

    XmlNode node = doc.SelectSingleNode("//EVENT_INSTANCE/Database/text()");
    string database = node.Value;
    node = doc.SelectSingleNode("//EVENT_INSTANCE/Object/text()");
    string user = node.Value;

    EventLog evt = new EventLog("Application", ".", "DBAudit");
    evt.WriteEntry(string.Format("User {0} created in database {1}", 
                                 user, database));
  }
}

So first of all I get hold of the details about why this trigger has fired in the form of a SqlTriggerContext. I then extract the XML from the EventData property which is of SqlChars type). Using XPath I get the user name and database, then add an entry to the event log.

OK, so we have the code, but now we need to expose the functionality within the database. Here's the SQL to install the trigger:

create assembly Triggers
from 'C:\trigger\triggers.dll'
with permission_set=UNSAFE
go

create trigger AddUser
on database for create_user
as external name Triggers:CAddUser::AddUser
go

So there we have - a powerful extension without having to resort to extended stored procedures. The one thing I find frustrating is that I have to add the assembly to the UNSAFE CAS bucket otherwise it cannot write to the Event Log. To me it would seem that EXTERNAL_ACCESS should be enough - hopefully this will change by the next beta.