- Programming with SqlClient
- Context: The SqlContext Class
- Connections
- Commands: Making Things Happen
- Obtaining Results
- Transactions
- Pipe
- Creating and Sending New Rowsets
- Using the WindowsIdentity
- Calling a Web Service from SQLCLR
- Exception Handling
- SqlTriggerContext
- SqlClient Classes That You Can’t Use on the Server
- Where Are We?
This chapter is from the book
This chapter is from the book
This chapter is from the book
SqlTriggerContext
SQL triggers have an execution context (represented by SqlContext) just as stored procedures and UDFs do. The environment settings, temporary tables in scope, and so on are available to a trigger. But triggers have additional context information; in T-SQL, this information consists of logical tables for DML (data manipulation language) statements, as well as information about which columns were updated, in the case of an update statement. In CLR triggers, this information is made available through the SqlTriggerContext class. You obtain the SqlTriggerContext through the SqlContext, as shown in the following example. SqlTriggerContext has a property to tell you whether the triggering action was an INSERT, UPDATE, DELETE, or one of the new DDL or event triggers. This is handy if your trigger handles more than one action. The IsUpdatedColumn(n) returns information about whether the nth column in the table or view has been changed by the INSERT or UPDATE statement that caused the trigger to fire. Finally, because SQL Server 2005 adds DDL and Event triggers to the mix, a property that exposes the EventData XML structure provides detailed information.
The INSERTED and DELETED logical tables work the same way that they do in T-SQL triggers; they are visible from any SqlCommand that you create inside your trigger. This is shown in Listing 4-24. Because individual SQL statements (including triggers) are transactional, triggers in T-SQL use the ROLLBACK statement if the logic in the trigger determines that a business rule has been violated, for example. Although inside a trigger, the context transaction is visible, rolling it back produces an error just as in other CLR procedural code. The way to roll back the statement inside a trigger is to use the System.Transactions.Transaction.Current property. Listing 4-24 shows an example of this. Note that this example is meant only to show the coding techniques involved; using T-SQL actually would be a better choice for triggers like this.
Listing 4-24: A SQLCLR trigger that rolls back the transaction in progress
// other using statements elided for clarity
using System.Data.SqlClient;
using System.Transactions;
public static void AddToObsolete()
{
// get the trigger context so you can tell what is
// going on
SqlTriggerContext tc = SqlContext.TriggerContext;
// this trigger is for deletes
if (tc.TriggerAction == TriggerAction.Delete)
{
// Make a command
SqlConnection conn = new SqlConnection("context connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
// make a command that inserts the deleted row into
// the obsoletejobs table
cmd.CommandText = "insert into obsoletejobs select * from deleted";
// move the rows
int rowsAffected = (int)cmd.ExecuteNonQuery();
if (rowsAffected == 0)
{
// something bad happened, roll back
Transaction.Current.Rollback();
}
}
}