SQL Server + CLR -> Stuff noted

There appears to be many limitations and rules to be followed while using the CLR to write an SQL object:

. While using the context connection object via SqlConnection("context connection=true"), make sure only one connection object with this context is open at any point in time. 'Using' should be your friend here.

. If you want to initialize application data which needs to be re-used each time the function/trigger is called, you would want to use static variables. SQL Server would let you define readonly static variables only. (This effectively means, that all initialization has to happen at the constructor).

. Want to access a different database from within a function/any other CLR object ? Make sure the assembly has EXTERNAL_ACCESS granted.

. When your code starts to use locking/objects which use locking (most trace listeners do) , SQL Server complains like mad. At this point, you have no way out other than make sure that the login you created with the asymmetric key on the master DB has got the UNSAFE access.

. To make sure that debugging works OK, make sure that the symbols file (.pdb) is added to the assembly with an alter assembly add file statement.

. If you are accessing a different DB, the transaction for the current context would not be use full there since CLR would not let you. You would need to force a new transactionscope in this case.

As can be seen, there requires lots of hacks to make the stuff working in the CLR environment. The first draft of the code you wrote is guaranteed to not work with SQL CLR :)

May the CLR be with you.

SQL Server 2005 & A Global Trigger

With the introduction of CLR into SQL Server 2005, the programmer can now write functionalities exploiting the C# language and the vast .NET library. A closer look at integrating CLR trigger handlers reveals many interesting stuff:

Classes of concern
Namespace Microsoft.SqlServer.Server contains all of the classes we would use in a SQL CLR object. While writing triggers, we would be exposed to:

SqlContext - gives you back the execution context to get more information;In the case of triggers, we would use the SqlTriggerContext to get the TriggerAction - delete/insert/update, columns updated etc.

SqlPipe - This acts as the channel/pipe to send back stuff to SQL Server, ranging from debug messages to recordsets; using the Send method.

SqlTrigger Attribute - These attributes appear to be used only by the Visual Studio environment while deploying the CLR object (I think!), since most of these parameters (Target, Event) can also be specified using DDL statements.

Writing Trigger in CLR & Integrating
Once you create a DB project in SQL Server 2005, writing a trigger is quite simple. Create a new class, create a new public method with the SqlTrigger Attribute applied to it. Within the procedure you would want to use the SqlTriggerContext and perhaps the SqlPipe object (as described above)

Once everything is compiled right, go to your T-SQL editor and perform the following [you could also deploy the assembly directly from within VS.NET; but then, where is the fun? :)]

a.) create an assembly within SQL Server, pointing to our .NET dll file. You would need to specify EXTERNAL ACCESS if you are looking at cross database updations from within the trigger, which would also need special permissions.

b.) create the trigger against your table using the CREATE TRIGGER with EXTERNAL name pointing to the assembly.namespace.class.function.

c.) Thats it!

Global Trigger
Its interesting to note that if you havent specified a Target in the SqlTriggerAttribute, you could reuse the same assembly object for any number of tables. You just have to create a trigger against the table in concern using our assembly object. This way, you effectively have a single trigger codebase running against all of the tables. Easy to maintain :)

Accessing the Table Name from within Trigger
Unless you are writing trigger for a single table, you might want to know the table name for which the trigger is executing. There appears to be no straight property/method to do this (Why couldnt the context object just return this?) and what could be figured :
SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'

This basically gets the object name which is locked in this session. (a very dirty hack ,unless someone figured out a better way?)