How to create CLR Trigger

Steps for Creating CLR Trigger

The following are required steps for creating a CLR-Trigger of DML (After trigger) type for Insert action. This database Object is executed as the result of a user action against a table i.e. for an INSERT statement.          Creating a .NET class of triggering action          Making assembly(.DLL) from that Class          Enabling CLR environment in that database.          Registering the assembly in SQL Server          Creating CLR Trigger using that assembly 1. Creating a .NET class Here we can use any managed language that is supported by .Net Framework such as C++, C#, VB, J#, JScript or XAML, etc. As I am with VB, this managed code is in Visual Basic. Let us discuss the objective of this entity. According to the above example of "tr_User_INSERT" trigger, we have the UserTable for holding the user details. There is a field "Type" which explains the user role (ADMIN, End User, Register User etc.).  Our objective is to check the role of the inserted User for ADMIN type and then do the action as we set. Open the notepad, copy the following codes and save it as MyFirstAssembly.vb. Listing 2 (.NET Class of Trigger type) Imports System.Data Imports System.Data.SqlClient Imports Microsoft.SqlServer.Server Partial Public Class MyFirstClrTrigger <Microsoft.SqlServer.Server.SqlTrigger(Name= "checkUserRole", Target= "UserTable", Event = "FOR INSERT")> _    Public Shared Sub checkUserRole()   Dim triggContext As SqlTriggerContext = SqlContext.TriggerContext() Dim userType As String = String.Empty   If triggContext.TriggerAction = TriggerAction.Insert Then   Using connection As New SqlConnection("context connection=true")   connection.Open()   Dim sqlComm As New SqlCommand   Dim sqlPipe As SqlPipe = SqlContext.Pipe()     sqlComm.Connection = connection   sqlComm.CommandText = "SELECT Type from INSERTED"     userType = sqlComm.ExecuteScalar.ToString()     If userType.ToUpper = "ADMIN" Then       sqlPipe.Send("Hello !!! You have the Admin role.")     sqlPipe.Send("We can use e-mail codes here to inform.")     End If   End Using End If End Sub   End Class    Let us go into the codes. There are two major Namespaces used, System.Data.SqlClient and Microsoft.SqlServer.Server. Microsoft.SqlServer.Server provides the SqlTriggerAttribute Class, which is used to mark a method definition in an assembly as a trigger in SQL Server. The Sqltrigger attribute requires some parameters to set the created trigger properties, such as Name - the name of the Trigger, Target - the table or view to which the trigger applies (in case of DML type) and Event - the event to fire the trigger for (e.g. FOR INSERT, DELETE and/or UPDATE or INSTEAD OF etc.). Again, that method must be a Static (Shared in VB) one; here checkUserRole() is the target method.  The SqlTriggerContext class provides the required triggering properties of the Trigger for doing action. TriggerAction property of this Class is the global enumeration TriggerAction type of Microsoft.SqlServer.Server namespace which indicates what action fired the Trigger. Here we use the INSERTED table, which is automatically created and managed by SQL Server 2005. This is used to set the conditions of DML trigger action. There is also another called DELETED, used in case of delete action. The CLR triggers can access the Inserted or Deleted tables through SqlCommand object using context connection. 2. Making assembly (.DLL) Now we have created the MyFirstAssembly.vb file with MyFirstClrTrigger class, so we need to compile the class to create an assembly. Here I use the VB compiler "vbc.exe," found in .Net   Framework library. Use the following DOS command for creating assembly. Listing 3 (compile the .VB file) C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>vbc /t:library /out:F:\CLR_Trigger_Test\CLR_Assembly\MyFirstAssembly F:\CLR_Trigger_Test\MyFirstClrTrigger.vb This command creates a DLL called MyFirstAssembly.dll at out path F:\CLR_Trigger_Test\CLR_Assembly\. 3. Enabling CLR environment By default, the CLR functionality is trued off in SQL Server; so we need to enable it. Use the following T-SQL codes to enable the CLR functionality. Listing 4 (enable CLR action) ------ Enabling CLR action in database ------- sp_configure 'clr enabled', 1 Go RECONFIGURE with Override Go 4. Registering the assembly Our next step is to create an assembly in the Database, based on the compiled DLL (MyFirstAssembly.dll). The following T-SQL codes are useful regarding this objective. Listing 5 (registering assembly) ------ Registering an Assembly ------- Create Assembly UserAssembly From 'F:\CLR_Trigger_Test\CLR_Assembly\MyFirstAssembly.dll' With Permission_Set=Safe Go 5. Creating CLR Trigger Now we will create an extended Trigger using CREATE Trigger statement of T-SQL. There is a new clause named EXTERNAL NAME in SQL Server 2005, which allows us to reference a method of the Registered assembly. By doing so we set the triggering action of our Trigger using that managed code method of the assembly. Listing 6 (creating a Trigger) ------ Creating a DML trigger in CLR ------- Create Trigger tr_User_CheckRole on UserTable For INSERT AS External Name UserAssembly.MyFirstClrTrigger.checkUserRole Go Here we use the checkUserRole() shared method of our MyFirstClrTrigger class of the registered assembly UserAssembly.