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?)

The Jukebox Issue

Problem at hand
What we had in place was a jukebox system wherein the client application on user machines would request songs from the main play list on the server. The server would pick up the next song in this queue and play it using media player on the server machine. The sound output is directed to the music system such that everyone around could listen to the many songs requested by others.

Everything appeared to go well until the non-jukie fellows started complaining - they did not want to hear to these songs, usually a different language/dialect. The music system had to go :(

Requirement
Now how do we fix this problem ? Music enthusiasts needed to be able to listen songs requested by others from their individual machines, perhaps using the headphones such that the non-jukies are not bothered.

Solution
Instead of directing the output from mediaplayer to the music system, use windows media encoder 9 series on the juke box server machine. Windows media encoder could encode music coming out from the soundcard into a live stream.

We needed a Win2003 box with Windows Media Services 9 series installed (comes with SP2) which can broadcast streams. Once we have this, setup the windows media services to feed from the media encoder stream off the jukebox server. What is remaining is simple. Ask all our dear enthusiasts to use Windows media player to listen to the live stream off the 2003 box. This way, the enthusiasts listen to the songs requested by other enthusiasts without disturbing the non-jukies.

Peace reigns once again.