Implementing CLR Triggers

Many, if not most, of the SQL Server databases in production today include one or more triggers. These form the last bastion of defense for your data. A trigger is simply a stored procedure that's executed automaticallyyou decide when. Since the earliest versions of SQL Server, triggers have been programmable to fire when new data is inserted, updated, or deleted on a specific table. In SQL Server 2005, the role of triggers has been greatly expanded to include data manipulation language (DML) operations of all kinds. It's really beyond the scope of this section to get into these advanced types of triggers, so I'll just stick with FOR INSERT, UPDATE, and DELETE triggersbut written in a CLR language.

For this example, I added a FOR INSERT, UPDATE trigger on the Loans table updated by the sample LoanRequest application, discussed earlier in this chapter. The stored procedure called in that example executed an INSERT to the Loans table. One of the business rules I imposed was that no loan could be accepted (or altered) that has a payment below a certain amount. If the payments are not high enough, the loan is rejected and the transaction rolls back. If the loan is accepted, both the Loans and Products tables are updated to reflect the sale of the property lot. In order to determine whether the loan payment is high enough, I had to incorporate a block of code that executed one of the .NET Framework's financial functions (pmt). This function, given a set of loan constraints, figures out how much the monthly payment would be. No, this functionality is not available in T-SQL, so it had to be implemented in CLR code or somehow cobbled together in T-SQL at considerable cost.

Because the trigger needs access to the current transaction state, I must add a reference to System.Transactions.DLL to the project. I also added an Include to help reference these classes at design time.

Building the CLR Trigger

You know the drill. Building a new trigger is really no different than building any of the other CLR executablesat least, it starts out the same. Of course, when creating any new SQL Server CLR executable, you'll have to specify the database where this UDT is to be deployed. Choosing "Add New Item | Trigger" creates a new template that includes a number of attributes specific to the trigger (as shown in Figure 13.102).

Figure 13.102. The unaltered CLR Trigger template.

As with any other application, you'll have to add a reference to any CLR UDTs to the project so that they can be accessed by the compiler. In this case, I added a reference to the UDT typICurrencyV2.

These attributes are used to give the compiler and SQL Server enough information to build and deploy your UDT trigger:

• Name: This names the trigger. That's not particularly important, as this name is referenced only from within SQL Server.

• Target: This attribute points to the database table where the trigger should be deployed.

• Event: This attribute indicates which events fire the trigger. I'll set this to FOR INSERT, UPDATE so that the trigger fires as new rows are added or changed. For this example, I don't care about the other operations.

The code implemented to be fired when the target table (Loans) is changed by an INSERT or UPDATE operation is not that complex. I start by picking up the SqlTriggerContext, which tells me which of the bazillion operations have occurred. Theoretically, the trigger code should not be called unless the TriggerAction property is set to TriggerAction.Insert or TriggerAction.Update. I use a Case statement (as shown in Figure 13.103) to route execution to the appropriate routine based on the TriggerAction.

Figure 13.103. Routing execution in the trigger based on the TriggerContext.TriggerAction.

Accessing the INSERTED and DELETED Pseudo Tables

One unique attribute of any trigger that monitors the FOR INSERT, DELETE, or UPDATE operations is the INSERTED and DELETED pseudo tables. When SQL Server executes an INSERT statement, it saves the contents of the new row to a pseudo (in-memory) table that can be tested in your trigger code. Many DBA set up triggers that implement a wealth of business rules to make sure the new row meets all of the required rules and regulations as dictated by their business and the referential integrity constraints of the database. If the new row does not pass muster, the transaction is rolled back and the change is rejected.

In a similar way, when an UPDATE operation is begun, SQL Server copies the existing row to the DELETED pseudo table and the changed row to the INSERTED pseudo table. As before, the trigger code can validate the change and inspect changed columns to see if they still comply with the rules and constraints currently being enforced.

Note that when you execute an UPDATE or DELETE statement, the operation can span many rows. Because of this, the UPDATED and INSERTED pseudo tables can contain any number of rows. The Trigger needs to be able to deal with all of these rows in succession. Note that when you use the ADO.NET Update method (as exposed on the SqlDataAdapter and the TableAdapter), each UPDATE is submitted individuallyeven in batch mode. ADO.NET makes no attempt to correlate the UPDATE operations. Sure, you can create an ADO.NET SqlCommand object to execute a single UPDATE or DELETE operation that spans many rowsbut you'll have to roll that T-SQL statement yourself.

As shown in Figure 13.104, the INSERTED pseudo table is accessed like any other table in SQL Servervia a SqlDataReader. Note that I must use a context connection to access the list of changes exposed in the INSERTED table.

Figure 13.104. Accessing the INSERTED pseudo table.

The logic in the second half of the HandleInsert routine (shown in Figure 13.105) walks through the row(s) of the INSERTED table and calculates whether the payment computed by the client application is not only correct, but meets the business rules. In this case, I set the criteria for a "valid" loan at 1100 (monetary units). Yes, this is not the same as the stored procedure, which accepts loans above 1,000 units, but I wanted to use this to illustrate how one tier can accept data and another reject it.

Figure 13.105. Executing the business rule and rolling back the transaction.

If the logic determines that the loan is unacceptable for any reason, I pick up the current transaction status and roll back the transaction.

To get access to the Transaction class, you'll need to add a reference to System.Transactions.DLL to your project.

Handling the Update

When an UPDATE operation changes the targeted table, your trigger fires with the appropriate TriggerAction set to Update. In this case, the Framework exposes a count of the columns in the UPDATED table, which can, in turn, be used to dimension a Boolean array to test to see which columns have changed. Once this array is populated, you can walk through the values to fire column-specific testsbut just on changed columns (if that makes sense). In this case, I know that column 6 contains the payment, and it's the only one I care about. I perform the same test on this column using the HandleInsert routine.

Figure 13.106. Handling UPDATE operations.