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.
To learn how to create a SQL server trigger in SQL CLR and use it.
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
public partial class Triggers
//[Microsoft.SqlServer.Server.SqlTrigger(Name = "SaleOrderDetailAudit", Target="SalesOrderDetail", Event = "FOR UPDATE")]
public static void SaleOrderDetailAudit()
SqlPipe sqlP = SqlContext.Pipe;
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"))
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = conn;
sqlCommand.CommandText = "SELECT SalesOrderID, OrderQty FROM INSERTED";
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
paramSalesOrderID.Value = sqlDataReader.GetValue(0);
paramOrderQty.Value = sqlDataReader.GetValue(1);
sqlCommand.CommandText = "INSERT INTO dbo.SalesAudit(SalesOrderID,OrderQty,AuditDate) VALUES (" + paramSalesOrderID.Value +", " + paramOrderQty.Value + ", GETDATE())";
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.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
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]
SET ANSI_PADDING ON
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).
CREATE TRIGGER SaleOrderDetailAudit
EXTERNAL NAME SqlServerproject.Triggers.SaleOrderDetailAudit
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.
SET [OrderQty] = 1
WHERE [SalesOrderID]= 43659
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.
Please see this link.