Sunday, January 01, 2017

SQL 2005 - Creating And Using CLR Trigger using C#

1. SQL CLR Trigger

 In the last article, we saw creating a SQL CLR Function. I would suggest reading that article first before reading this one. The article is here.

Like CLR function, one can write SQL Trigger also in the C-Sharp. In this article, we will learn to create the SQL CLR Trigger and executes that in SQL Server Management studio.

2. Prepare Table for Trigger Target


In this Example, we are going to write a CLR Trigger that does not allow entering the Negative Bonus in the “Emp” table. Run the below script in Pubs database to create the Emp table and populate three sample records into it.

--Create Table
Create Table Emp(EmpId int, EmpName varchar(20), Bonus Numeric(5));

--Insert Data
Insert into Emp values(100, 'Subramaniyan', 12000);
Insert into Emp values(101, 'PeteJones', 18000);
Insert into Emp Values(102, 'AbdulRehman', 14000);

The trigger that we are going to write will prohibit the negative value in the Bonus column of the Emp table.


3. Creating the SQLCLR Trigger


First, create the Database project by Clicking the Database under project type and “SQL Server Project” under Templates. This is shown in the below screenshot.

Fig 1. Create Database project

Once this is done, add the Trigger item to the project. To do this Right Click on the project name, and then choose “Add=>New Item”. Then from the displayed dialog, select the Trigger as Item Type (Marked as 1), provide a name for the trigger (Marked as 2) and finally click the Add button (Marked as 3). This is shown in the below screenshot:

Fig 2. Add CLR Trigger to Project

Now, the CLR Trigger is added to the project. You can see these steps in the below video:

Video 1: Creating start-up project for CLR-Trigger


4. Implementing CLR-Trigger 

1) First, we need to add the SQL Transaction reference to the project. We will use this reference for canceling the transaction when a negative bonus enters the Bonus column of the Emp Table. To add the reference, follow the steps below:
  1. Expand the project and right-click reference and from the displayed context menu, we need to choose “Add Reference…” (Marked as 1). 
  2. In the “Add Reference” dialog, switch to SQL Server tab (Marked as 2) 
  3. Select the “System.Transactions” item (Marked as 3) and click OK (Marked as 4).

Fig 3. Adding System.Transaction Reference

2) After adding the reference, we are ready to start our coding. The code is given in the screenshot below (Refer Listing 1 at the end of the article).

Fig 4. SQL-CLR Trigger Code Example
  1. Here, first a “using statement” is added so that we can rollback the transaction (Marked as 1). Remember that we added a reference in the previous step.
  2. Next, the “SqlTrigger” attribute is modified. We provided a name for our SQL CLRTrigger, which is ‘MyClrTrigger’ (Marked as 2). The trigger target is specified using “Target” property and in our case, it is Emp table (Marked as 3). Finally, we specified that this trigger should fire when Insert and Update are carried out on EMP table. We used the “Event” property of the attribute class for this (Marked as 4).
  3. When trigger fires, we are selecting the count of records with a negative bonus by querying the “INSERTED” virtual table (Read Trigger article from SQL 2005 section to know more about INSERTED, DELETED). Note that we used context connection for opening a session with SQL Server. (Marked as 5)
  4. After the connection is opened, we are making a call to “ExecuteScalar()” to get a number of records with Negative bonus (Marked as 6). When there is at least one negative bonus record, we need to cancel the transaction.
  5. When a negative bonus is found, a “SqlPipe” instance “MessagePipe” is created from “SqlContext.Pipe”. Next, a message is sent to the SQLServer using the “Send() method” (Marked as 7) stating that negative bonus is not allowed. Note that the message gets displayed in the message tab of Management Studio.
  6. After sending the message, the current transaction is canceled by calling Rollback() method of the Current Transaction object. 

5. Testing the CLRTrigger

 After deploying the application, the query can be executed in the SQL Server management studio. When we try to enter a negative bonus, the CLR Trigger displays the message and rollbacks the transaction. This is shown below:

Fig 5. Executing SQL-CLR Trigger

In the above example, we are trying to insert a record with negative bonus (Marked as 1). This statement is executed in between “Begin Try” and “End Try” blocks (Marked as 2). This shows a message that we formed in the C-Sharp Code (Marked as 3 & 4). The source code and executing it is explained in the below video.

Video 2: SQLCLR Trigger Code Explained


6. Code Listings

Listing 1.1


using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

// Sample 01: To Rollback the transaction
using System.Transactions;


public partial class Triggers
{
    // Enter existing table or view for the target and uncomment the attribute line
    [Microsoft.SqlServer.Server.SqlTrigger(
        Name = "MyClrTrigger",
        Target = "Emp",
        Event = "FOR Update, Insert")
    ]
    public static void MyClrTrigger()
    {
        //Create and Open SQL Connection
        SqlConnection con = new SqlConnection("context connection = true");
        string SqlStr = "Select Count(*) from INSERTED where Bonus < 0";
        SqlCommand cmd = new SqlCommand(SqlStr, con);
        con.Open();

        //Check the Inserted Column with Negative number, and Raise Error
        int numcolumn = (int)cmd.ExecuteScalar();
        if (numcolumn > 0)
        {
            //Use the SQL Pipe to send the Error text (Displayed in Messages Tab)
            SqlPipe MessagePipe = SqlContext.Pipe;
            MessagePipe.Send("Negative Bonus Not Allowed!");

            //Now Rollback the Transaction
            Transaction.Current.Rollback();
        }
    }
}
SourceCode: Download


No comments:

Post a Comment

Leave your comment(s) here.

Like this site? Tell it to your Firend :)