Share via

CLR Trigger for Automatic Registration of UDXs on CREATE ASSEMBLY

  • Article

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

Cheers,
Miles Trochesset - Microsoft SQL Server

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

Comments

  • Anonymous
    December 01, 2005
    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.
  • Anonymous
    December 05, 2005
    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?
  • Anonymous
    December 08, 2005
    The comment has been removed
  • Anonymous
    December 28, 2005
    Hi Ashish,
    SQLServer uses public hosting interfaces that come with .Net Framework 2.0.
    More details at:
    http://msdn2.microsoft.com/en-us/library/9x0wh2z3.aspx
  • Anonymous
    April 23, 2006
    The comment has been removed
  • Anonymous
    August 28, 2007
    PingBack from http://tsql.mambo5.ru/archives/59