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.