SQL CLR Trigger Explained

Table of Contents

1. About 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. 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 execute that in SQL Server Management studio.

2. Prepare Emp Table

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);

--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. Create Database Project

First, we create the DB project by clicking the Database under project type and SQL Server Project under Templates. The below picture shows this:

After creating the project, 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). The Add New Item dialog below shows the steps:

We added the CLR Trigger to our project. You also see these steps in the below video.

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

4. CLR-Trigger For Negative Bonus Transaction

4.1 Add System.Transactions Namespace

First, we need to add the SQL Transaction reference to the project. We will use this reference for cancelling the transaction when a negative bonus enters the Bonus column of the Emp Table. The below steps show how to add this reference:

Expand the project and right-click reference. From the displayed context menu, we need to choose Add Reference… (Marked as 1).
In the Add Reference dialog, switch to SQL Server tab (Marked as 2).
Select the System.Transactions item (Marked as 3) and click OK (Marked as 4).

Refer the below picture to know the steps:

4.2 Implementing CLR Trigger

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

Code Explanation

Here, we have a using statement (Marked as 1) so that we can use the transaction object. Remember that we added a reference in the previous step.
Next, we modify the SqlTrigger attribute. 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 this trigger should fire when the EMP table undergo Insert and/or Update. We used the Event property of the attribute class for this (Marked as 4).
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, we used context connection for opening SQL Server connection. (Marked as 5)
After opening the connection, 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.
When a negative bonus is found, we create a SqlPipe instance MessagePipe from SqlContext.Pipe. Next, we send a message to the SQLServer using the Send() method (Marked as 7) stating that database does not allow negative number. Note, message tab of the Management studio will receive this message.
After sending the message, we cancel the current transaction by calling Rollback() method of the Current Transaction object.

5. Testing the SQL CLR Trigger

After deploying the example, we can execute a sample query in the SQL Server management studio. When we try to enter negative bonus, the CLR Trigger displays the message and rollbacks the transaction. Refer to the picture below:

In the above example, we are trying to insert a record with a negative bonus (Marked as 1). We execute this statement in between Begin Try and End Try blocks (Marked as 2). This shows a message which 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

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();
        }
    }
}

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();

}

Categories: SQL Server

Tags: CLR Trigger, SqlContext, SqlPipe, SqlTrigger, System.Transaction

Do you like this Example? Please comment about it for others!!Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Programming Examples

Are you a Programmer or Application Developer or a DBA? Take a cup of coffee, sit back and spend few minutes here :)

1. About SQL CLR Trigger

2. Prepare Emp Table

3. Create Database Project

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