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

Vishvvas
Posted by in .NET Framework category on for Advance level | Points: 300 | Views : 5343 red flag

This is next write-up in the multipart series for SQL Server Integration with CLR aka SQL CLR and discusses the creation of SQL server trigger through 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);                

            }

        }

    }

}

 

Is it all done? If yes, then where would the trigger save the records? Definitely, we are not done yet and we need to create a table for storing the audited records.

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.

Page copy protected against web site content infringement by Copyscape

About the Author

Vishvvas
Full Name: Vishwas Sutar
Member Level: HonoraryPlatinum
Member Status: Member,MVP
Member Since: 5/30/2011 2:13:10 AM
Country: India

http://www.dotnetfunda.com
Extensive and rich experience across gamut of technologies and programming languages like PB,VB,C++,VB.NET, C#, Classic ASP,ASP.NET, ASP.NET MVC.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)