SQL Server integration with CLR aka SQL CLR (Part V – Creating a SQL server trigger in SQL CLR)

Introduction and background

In the first article, we discussed what is SQLCLR, why SQLCLR and the steps for employing SQL CLR into our work. The next articles -->we explored how to create a stored,  we learnt to create a UDF, we ran through how to create SQL aggregrate and in this article, we would see the creation a SQL server trigger in SQL CLR.

Trigger, which is special kind of stored procedure and it is executed automatically when a DML operation is performed on specific table. Trigger is always associated with specific table and can be multiple in numbers. Triggers are good option for enforcing the business rules and data integrity scenarios. The most common usages are for auditing, logging and also perform cascade operations.

Objective

To learn how to create a SQL server trigger in SQL CLR and use it. 

Description

Note: The tool for development is VS 2010 and SQL server 2008 R2. The database “AdventureWorks” is referred.

The first step in learning the SQL CLR database items is to create a SQL CLR project and a trigger can be added to such project as following.

When a trigger is added, the Visual Studio adds a class in the file as shown below. The static function would contain the definition for trigger and also the functionality for the trigger.

We would have a trigger for auditing the update operation on [Sales].[SalesOrderDetail] capturing the order quantity , the data of change etc. This is depicted in following code snippet

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
 
 
public partial class  Triggers
{    
    //[Microsoft.SqlServer.Server.SqlTrigger(Name = "SaleOrderDetailAudit", Target="SalesOrderDetail", Event = "FOR UPDATE")]
    public static void SaleOrderDetailAudit()
    {
        SqlPipe sqlP = SqlContext.Pipe;
        
        sqlP.Send("Trigger FIRED");
 
        SqlTriggerContext triggContext = SqlContext.TriggerContext;
        SqlParameter paramSalesOrderID = new SqlParameter("@salesOrderID", System.Data.SqlDbType.Int);
        SqlParameter paramOrderQty = new SqlParameter("@orderQty", System.Data.SqlDbType.SmallInt);         
 
        if (triggContext.TriggerAction == TriggerAction.Update)
        {
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                conn.Open();
                SqlCommand sqlCommand = new SqlCommand();               
 
                sqlCommand.Connection = conn;
                
                sqlCommand.CommandText = "SELECT SalesOrderID, OrderQty FROM INSERTED";
 
                sqlP.Send(sqlCommand.CommandText);
 
                sqlP.ExecuteAndSend(sqlCommand);
 
                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();             
 
              
                while (sqlDataReader.Read())
                {                    
                    paramSalesOrderID.Value = sqlDataReader.GetValue(0);
                    sqlP.Send(paramSalesOrderID.Value.ToString());
                    paramOrderQty.Value = sqlDataReader.GetValue(1);
                    sqlP.Send(paramOrderQty.Value.ToString());              
                }
 
                sqlCommand.Parameters.AddWithValue("@salesOrderID", paramSalesOrderID.Value);
                sqlCommand.Parameters.AddWithValue("@orderQty", paramOrderQty.Value);
 
                sqlDataReader.Close();
                sqlCommand.CommandText = "INSERT INTO dbo.SalesAudit(SalesOrderID,OrderQty,AuditDate) VALUES (" + paramSalesOrderID.Value +", " + paramOrderQty.Value + ", GETDATE())";
 
                sqlP.Send(sqlCommand.CommandText);                
                sqlP.ExecuteAndSend(sqlCommand);                
            }
        }
    }
}

USE [AdventureWorks]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[SalesAudit](
      [DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
      [SalesOrderID] [int] NOT NULL,
      [OrderQty] [smallint] NOT NULL,
      [AuditDate] [datetime] NOT NULL,
      [UpdatedUser] [varchar](20) NULL
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING ON
GO
 

Once this script is executed, the database table appears in the list.

The build and deployment (shortcut key Ctrl + F5) process is same in Visual Studio as it is for other database items like stored procedure and UDF etc.

Through TSQL, the trigger can be created as follows. (This step is to be performed after the registration of assembly).

USE [AdventureWorks]
GO 
CREATE TRIGGER SaleOrderDetailAudit
 
ON [Sales].[SalesOrderDetail]
 
FOR UPDATE
 
AS
 
EXTERNAL NAME SqlServerproject.Triggers.SaleOrderDetailAudit
GO

Yeah, we are created our first trigger and deployed too. The trigger would enlist in the triggers associated with the table. Refer following image.

This is the time to test the functioning of the trigger. Following T SQL needs to be put in “Test.sql” for testing through Visual studio or we can test this T SQL on SQL server management studio.

UPDATE [AdventureWorks].[Sales].[SalesOrderDetail]
   SET [OrderQty] = 1    
 WHERE [SalesOrderID]= 43659
GO
SELECT [DatabaseLogID]
      ,[SalesOrderID]
      ,[OrderQty]
      ,[AuditDate]
      ,[UpdatedUser]
  FROM [AdventureWorks].[dbo].[SalesAudit]

The results can be seen in “Database Output” as below.

Creating, building, deploying and running (testing) a SQLCLR trigger is also straightforward as for other database items and with VS 2010 support for build and deploy and also provision for test output in one go, is great booster.

Summary and Conclusion

We are through our first exercise of creating a trigger in SQL CLR along-with deployment and testing. We have seen the Visual Studio as well as T SQL deployment. Definitely the efforts for creating trigger aren’t too time consuming and worth of considering for future projects.

Hope this helps to help understand how to create SQL CLR trigger and start mastering the SQL CLR world.

HAPPY PROGRAMMING!!!

Reference

Please see this link.