CLR Trigger for Automatic Registration of UDXs on CREATE ASSEMBLY

Using a CLR trigger we can register all user defined functions, procedures, types, aggregates etc., contained in an assembly when it is created in the database on “CREATE ASSEMBLY … “. This saves us the time of registering all these ourselfves. We skip the “CREATE PROCEDURE …”, “CREATE AGGREGATE …” steps usually necessary after registering the assembly.

Be sure to look at the code to see how this CLR trigger works. We use reflection to look through the assembly. When a type or function is found that needs to be registered, we look at the SQL custom attributes to find relevant information for that UDX (don’t forget these attributes when you develop your assemblies). For example when the assembly contains a CLR trigger, the SQLTriggerAttribute is examined to find the event on which the CLR trigger will fire, the name and target for the trigger. Finally we use an in proc connection to send TSQL commands and register what is found in the assembly.

The code for this trigger is available here

Miles Trochesset – Microsoft SQL Server

This posting is provided “AS IS” with no warranties, and confers no rights.

Comments (6)

  1. marcus says:

    From within a CLR Trigger, how do you get the name of the table? I know you can use the Inserted/Deleted tables, but I want to know the name of the table that had the trigger on it. Failing that, I could even use the trigger name and put the table name into that and parse it out. It looks like @@PROCID and OBJECT_NAME() is the way to get that in T-SQL but it doesn’t work right in a CLR trigger (which the docs mention without offering an alternative)…

    This is so I can have a generic trigger that can be used on multiple tables which does different things based on which table it’s on.

  2. Ashish Sheth says:

    Where can I find resource/articles that describes when How SQL Server loads and hosts the CLR? Does SQL server has its own CLR or it’s shared one?

  3. Miles Trochesset says:

    At this moment, there is no easy way of obtaining the table name from a CLR trigger. Below I give two ways of working around this.

    1st solution:

    – Add a String argument to the CLR Trigger to pass the table name

    – Create a user defined function for each table that will have this trigger registered on.

    public partial class Triggers


    private static void Trigger(String tableName)


    // You can now differentiate the trigger’s behavior depending on the value of tableName

    SqlContext.Pipe.Send("This is a trigger on " + tableName);



    public static void Function_A()





    public static void Function_B()





    Then you register a trigger on table A as


    ON A


    AS EXTERNAL NAME Assembly.Triggers.Function_A

    and you register a trigger on table B as


    ON B


    AS EXTERNAL NAME Assembly.Triggers.Function_B

    2nd Solution:

    Use the dm_tran_locks to retrieve the name of the table. Unfortunately this workaround will work only if your trigger doesn’t

    hold a bunch of locks everywhere.

    public partial class Triggers


    public static void GetTable()


    SqlConnection conn = new SqlConnection("Context Connection=true");


    SqlCommand cmd = new SqlCommand("select object_name(resource_associated_entity_id) from sys.dm_tran_locks where

    request_session_id = @@spid and resource_type = ‘OBJECT’", conn);




  4. beysim says:

    Hi Ashish,

    SQLServer uses public hosting interfaces that come with .Net Framework 2.0.

    More details at:

  5. John says:

    OK, this is a cool little thing to have when you are debugging. But what would be even cooler would be a command line tool that generates assembly install and uninstall t-sql scripts… Just planting the seed here 🙂

    Maybe I will get at it when I have some time and less deadlines…