Triggers and CLR

Triggers, as well as stored procedures and user-defined functions, can be implemented using the Common Language Runtime (CLR). The following steps are necessary if you want to implement, compile, and store CLR triggers:

• Implement a trigger using C# or Visual Basic and compile the program using the corresponding compiler (see Examples 14.9 and 14.10).
• Use the CREATE ASSEMBLY statement to create the corresponding executable file (see Example 14.11).
• Create the trigger using the CREATE TRIGGER statement (see Example 14.12).

The following examples demonstrate these steps. Example 14.9 shows the C# source program that will be used to implement the trigger from Example 14.1.

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class StoredProcedures
{
	public static void Modify_Budget()
	{
		SqlTriggerContext context = SqlContext.TriggerContext;
		if(context.IsUpdatedColumn(2)) //Budget
		{
			float budget_old;
			float budget_new;
			string project_number;
			SqlConnection conn = new SqlConnection("context connection=true");
			conn.Open();
			SqlCommand cmd = conn.CreateCommand();
			cmd.CommandText = "SELECT budget FROM DELETED";
			budget_old = (float)Convert.ToDouble(cmd.ExecuteScalar());
			cmd.CommandText = "SELECT budget FROM INSERTED";
			budget_new = (float)Convert.ToDouble(cmd.ExecuteScalar());
			cmd.CommandText = "SELECT project_no FROM DELETED";
			project_number = Convert.ToString(cmd.ExecuteScalar());
			cmd.CommandText = @"INSERT INTO audit_budget
					VALUES(@project_number, USER_NAME(), GETDATE(),
					@budget_old, @budget_new)";
			cmd.Parameters.AddWithValue("@project_number",project_number);
			cmd.Parameters.AddWithValue("@budget_old",budget_old);
			cmd.Parameters.AddWithValue("@budget_new",budget_new);
			cmd.ExecuteNonQuery();
		}
	}
}Code language: PHP (php)

The Microsoft.SQLServer.Server namespace comprises all client classes that a C# program needs. SqlTriggerContext and SqlFunction are examples of the classes that belong to this namespace. Also, the System.Data.SqlClient namespace contains classes such as SQLConnection und SQLCommand, which are used to establish the connection and communication between the client and a database server. The connection is established using the connection string “context connection = true”:

SqlConnection conn = new SqlConnection("context connection=true");Code language: JavaScript (javascript)

After that, the StoredProcedure class is defined, which is used to implement triggers. The Modify_Budget() method implements the trigger with the same name.

The instance of the SQLTriggerContext class called context allows the program to access the virtual table that is created during the execution of the trigger. The table stores the data that caused the trigger to fire. The IsUpdatedColumn() method of the SQLTriggerContext class allows you to find out whether the specified column of the table is modified.

The C# program contains two other important classes: SQLConnection and SQLCommand. An instance of SQLConnection is generally used to establish the connection to a database, while an instance of SQLCommand allows you to execute an SQL statement.

The following statements use the Parameters property of the SQLCommand class to display parameters and the AddWithValue() method to insert the value in the specified parameter:

cmd.Parameters.AddWithValue("@project_number",project_number);
cmd.Parameters.AddWithValue("@budget_old",budget_old);
cmd.Parameters.AddWithValue("@budget_new",budget_new);Code language: CSS (css)

Example 14.10 shows the execution of the csc command. Using this command, you can compile the C# program in Example 14.9.

csc /target:library Example14_9.cs
/reference:"c:\Program Files\Microsoft
SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlaccess.dll"Code language: PHP (php)

You can find the detailed description of the csc command in section “Stored Procedures & CLR“

Example 14.11 shows the next step in creating the modify_budget trigger. (Use SQL Server Management Studio to execute this statement.)

CREATE ASSEMBLY Example14_9 FROM
		'C:\Programs\Microsoft SQL Server\assemblies\Example14_9.dll'
		WITH PERMISSION_SET=EXTERNAL_ACCESSCode language: JavaScript (javascript)

The CREATE ASSEMBLY statement uses the managed code as the source to create the corresponding object, against which the CLR trigger is created. The WITH PERMISSION SET clause in this example specifies that access permissions are set to the value EXTERNAL_ACCESS, which does not allow assemblies to access external system resources, except a few of them.

Example 14.12 creates the modify_budget trigger using the CREATE TRIGGER statement.

CREATE TRIGGER modify_budget ON project
		AFTER UPDATE AS
	EXTERNAL NAME Example14_9.StoredProcedures.Modify_BudgetCode language: CSS (css)

The CREATE TRIGGER statement in Example 14.12 differs from the statement used in Examples 14.1 to 14.5 because it uses the EXTERNAL NAME option. This option specifies that the code is generated using CLR. The name in this clause is a three-part name. The first part is the name of the corresponding assembly (Example14_9), the second part (StoredProcedures) is the name of the public class defined in Example 14.9, and the third part (Modify_Budget) is the name of the method, which is specified inside the class.

Example 14.13 shows how the trigger in Example 14.3 can be implemented using the C# language.

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class StoredProcedures
{
	public static void WorksOn_Integrity()
	{
		SqlTriggerContext context = SqlContext.TriggerContext;
		if(context.IsUpdatedColumn(0)) //Emp_No
		{
			SqlConnection conn = new SqlConnection("context connection=true");
			conn.Open();
			SqlCommand cmd = conn.CreateCommand();
			cmd.CommandText = "SELECT employee.emp_no
				FROM employee, inserted
				WHERE employee.emp_no = inserted.emp_no";
			SqlPipe pipe = SqlContext.Pipe;
			if(cmd.ExecuteScalar() == null)
			{
				System.Transactions.Transaction.Current.Rollback();
				pipe.Send("No insertion/modification of the row");
			}
			else
				pipe.Send("The row inserted/modified");
		}
	}
}Code language: PHP (php)

Only the two new features used in Example 14.13 require description. The SqlPipe class belongs to the Microsoft.SQLServer.Server namespace and allows you to send messages to the caller, such as:

pipe.Send("No insertion/modification of the row");Code language: JavaScript (javascript)

To set (or get) the current transaction inside a trigger, you use the Current property of the Transaction class. Example 14.13 uses the Rollback() method to roll back the whole transaction after violation of the integrity constraint.

Example 14.14 shows the creation of the assembly and the corresponding trigger based on the C# program in Example 14.13. (Compilation of the C# program using the csc command as the intermediate step is necessary, but it is omitted here because it is analog to the same command in Example 14.10.)

CREATE ASSEMBLY Example14_13 FROM
	'C:\Programs\Microsoft SQL Server\assemblies\Example14_13.dll'
WITH PERMISSION_SET=EXTERNAL_ACCESS
GO
CREATE TRIGGER workson_integrity ON works_on
AFTER INSERT, UPDATE AS
EXTERNAL NAME Example14_13.StoredProcedures.WorksOn_IntegrityCode language: PHP (php)