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 Emp(EmpId int, EmpName varchar(20), Bonus Numeric(5));
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).
- 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
Emptable (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
MessagePipefrom 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
// Sample 01: To Rollback the transaction
public partial class Triggers
// Enter existing table or view for the target and uncomment the attribute line
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);
//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
Categories: SQL Server
Tags: CLR Trigger, SqlContext, SqlPipe, SqlTrigger, System.Transaction
Do you like this Example? Please comment about it for others!!