Programming Examples

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

SQL CLR Trigger Explained

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.

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:

Create Database Project
Create Database Project

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:

Add CLR Trigger to Project
Add CLR Trigger to Project

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:

  1. Expand the project and right-click reference. 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).

Refer the below picture to know the steps:

Add System.Transaction Reference
Add System.Transaction Reference

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

SQL-CLR Trigger Code Example
SQL-CLR Trigger Code Example

Code Explanation

  1. 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.
  2. 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).
  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, we used context connection for opening SQL Server connection. (Marked as 5)
  4. 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.
  5. 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.
  6. 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:

Testing the SQL CLR-Trigger
Testing the SQL CLR-Trigger

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

Categories: SQL Server

Tags: , , , ,

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

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