As promised in my last post, I’m going to tackle DDL triggers in SQL Server 2005. The only way to do this is through the feature SQL-CLR which was first introduced in the said version of SQL Server. Basically, with this feature you can create database objects using your favorite .NET language. I wrote an article a few months back about one of the objects you can create with SQL-CLR. DDL trigger is not as complicated as the one discussed there as you see momentarily.
The .NET Side
SQL Server 2005 DDL trigger is nothing more than a static void function adorned with Microsoft.SqlServer.Server.SqlTriggerAttribute as shown below
If you’re using Visual Studio Express or above, probably the only assembly you need to import is System.Xml. Everything else comes from the common set of assembly referenced by default. Notice that we left out namespace from our codes. I suggest you do this because namespaces have some weird effects to SQL Server when you catalogue your SQL-CLR objects. You may try otherwise but you’ve been warned.
The next step is optional. It merely checks whether or not your function is executed by SQL Server. It checks if the SQL Server context is present. If not, we should exit or else subsequent calls to context-sensitive objects would fail. Context-sensitive objects have values only when the function is running inside SQL Server. They can produce exception or behave unexpectedly when called outside SQL Server. If your 100% sure your functions will not be executed by consumers other than SQL Server,then you can skip this line.
Passing beyond this line guarantees that we are indeed inside SQL Server. Since we’re going to insert a record, we need a connection. Instead of creating a new one, we can use the connection that executes the function. We do this by supplying the argument “context connection = true” in one of the SqlConnection constructor overloads. To guarantee automatic closing and disposal, we use the using construct
Inside this block, we insert the records using a SqlCommand. It’s fine to use inline statement in this case because the codes are never run outside SQL Server and therefore SQL injection is not possible.
The schema of the ChangeLog table can be found in the last post.
The to insert comes from another context object SqlTriggerContext. This object is member of SqlContext and is only meaningful inside a trigger. It has a member EventData which is a SqlXml containing additional info about the event and object that fires the trigger. This acts as your EventArgs. This is exactly the same EventData I discussed in the last post so there’s no need to elaborate.
The final task in the .NET side is to execute the command. The values for the parameters are taken from the elements of the XML document. Navigating to those elements require XPath which like navigating to a folder. You can optionally raise an error for no insertion although this is very rare.
Build your project and copy your assembly to a shallow location. You may close your Visual Studio now; we’re done with the .NET side.
The SQL Server Side
The first step in the SQL Server side is to catalogue the assembly. This can be achieve by the CREATE ASSEMBLY command as shown:
You can catalogue the assembly with a different name but doing so might create confusion later on.
The next step is to catalogue the trigger. The syntax is very similar to that of the T-SQ version illustrated in the last post except for the body which follows after AS.
If you change something in the function other than renaming it, you can use ALTER ASSEMBLY command. This already takes care of the cataloguing of the trigger.
You’re trigger is now ready for reporting. You may issue a statement similar to the following and should get similar results:
You may catalogue your trigger in the model databases if you want this simple logging mechanism to be present in all your future databases.
No comments:
Post a Comment