CLR Triggers
Triggers written in a CLR language differ from other CLR integration objects in several significant ways. CLR triggers can:
- Reference data in the INSERTED and DELETED tables
- Determine which columns have been modified as a result of an UPDATE operation
- Access information about database objects affected by the execution of DDL statements.
These capabilities are provided by the SqlTriggerContext class. The SqlTriggerContext class cannot be publicly constructed and can only be obtained by accessing the SqlContext.TriggerContext property within the body of a CLR trigger. The SqlTriggerContext class can be obtained from the active SqlContext by calling the SqlContext.TriggerContext property:
SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;
The SqlTriggerContext class provides context information about the trigger. This contextual information includes the type of action that caused the trigger to fire, which columns were modified in an UPDATE operation, and, in the case of a DDL trigger, an XML Eventdata structure which describes the triggering operation. Once you have obtained a SqlTriggerContext, you can use it to determine the type of action that caused the trigger to fire. This information is available through the TriggerAction property of the SqlTriggerContext class.
CLR triggers can access the inserted and deleted tables through the CLR in-process provider. This is done by obtaining a SqlCommand object from the SqlContext object.For example,
SqlConnection connection = new SqlConnection(“context connection = true”);
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = “SELECT * from ” + “inserted”;
The number of updated columns can be determined by using the ColumnCount property of the SqlTriggerContext object.IsUpdatedColumn method can be used to determine whether the column was updated. This method takes the column ordinal as an input parameter, A True value indicates that the column has been updated.
reader = command.ExecuteReader();
reader.Read();
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
{
pipe.Send(“Updated column “+ reader.GetName(columnNumber) + “? “
+ triggContext.IsUpdatedColumn(columnNumber).ToString())
}
reader.Close();
User defined types
The user defined types allows the user to extend the scalar type system of the server. UDTs can contain multiple elements and can have behaviors, differentiating them from the traditional alias data types which consist of a single SQL Server system data type. A user-defined type is implemented as a managed class in any one of the CLR languages, and then registered with SQL Server. A user-defined type can be used to define the type of a column in a table, or a variable or routine parameter in the Transact-SQL language. An instance of a user-defined type can be a column in a table, a variable in a batch, function or stored procedure, or an argument of a function or stored procedure.
UDTs in SQL Server 2005 are well suited to the following:
- Date, time, currency, and extended numeric types
- Geospatial applications
- Encoded or encrypted data
The process of developing UDTs in SQL Server 2005 consists of the following steps:
- Code and build the assembly that defines the UDT.
- Register the assembly
- Create the UDT in SQL Server.Once an assembly is loaded into a host database, use the Transact-SQL CREATE TYPE statement to create a UDT and expose the members of the class or structure as members of the UDT. UDTs exist only in the context of a single database, and, once registered, have no dependencies on the external files from which they were created.
- Create tables, variables, or parameters using the UDT
]]>