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
For more information about this check my previous
article.
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.