Triggers are a very useful construct for T-SQL programmers. A routine can be defined that will automatically fire upon attempted data manipulation, thereby putting the onus for the required logic on the database itself, rather than every stored procedure that needs to manipulate it. An example of this would be a trigger used for auditing. By using a trigger, the logic for copying some of the modified data into another table is centralized. Without the trigger, every stored procedure that did anything with the data would need to have its own copy of this logic, and a developer might forget to include it in one stored procedure, thereby destroying continuity of the audit logs.
CLR triggers behave the same way as T-SQL triggers, bringing the same power to the table: centralization and encapsulation of logic. However, CLR triggers can be written in a .NET language and possibly take advantage of resources not easily accessible from T-SQL, such as regular expressions for data validation. CLR triggers can be used to define both DML (such as UPDATE, INSERT, and DELETE) and DDL triggers (such as CREATE TABLE). (See Chapter 13 for a discussion of DDL triggers.)
It’s important to remember when working with triggers that speed is of the essence. A trigger fires in the context of the transaction that manipulated the data. Any locks required for that data manipulation are held for the duration of the trigger’s lifetime. This means that slow triggers can create blocking problems, which can lead to severe performance and scalability issues. This concern is doubly important when working with the CLR. Triggers are not the place to contact web services, send e-mail, work with the file system, or do other synchronous tasks. Developers who need this functionality should investigate using technologies such as Service Broker. Remember that if you’re using CLR triggers, keep them simple!
Adding a CLR User-Defined Trigger to a SQL Server Project
To add a CLR trigger to an existing SQL Server project in Visual Studio 2008, right-click the project name in Solution Explorer and select Add ➤Trigger, as shown in Figure.
Adding a CLR trigger to a SQL Server project
Programming CLR Triggers
Once the trigger has been added to the project, Visual Studio will add template code. The following code is the result of adding a trigger called ValidateYear:using System;
This template is quite simplistic; programming a CLR trigger is very similar to programming a CLR stored procedure. The main differences between the two are the influence of the SqlTrigger attribute and the lack of a return value for triggers—the method that defines the trigger must return void. Aside from those differences, most programming paradigms hold true in both types of routines. CLR triggers, like CLR stored procedures, can make use of SqlPipe to return as many rowsets or messages to the client as the developer requires.
The SqlTrigger attribute’s primary function is to help Visual Studio or other third-party deployment tools determine for which tables and events the trigger is written. The following parameters are available for the attribute:
The SqlContext object exposes information about the state of the trigger via the TriggerContext.This object contains properties to assist with determining why the trigger fired. The most important of these are the TriggerAction property, which maps to an enumerator by the same name that contains every possible action that can cause a trigger to fire, and the EventData property, which contains XML data useful in DDL triggers.
For example, the following code fragment would be used to execute code conditionally based on whether the trigger had fired due to an update:if (SqlContext.TriggerContext.TriggerAction == TriggerAction.Update)
Validating a Year Using a CLR Trigger
It should be stressed once again that CLR triggers must be kept simple and quick, just like T-SQL triggers. There are few situations in which a pure CLR trigger is appropriate, given that CLR functions can be called from T-SQL triggers. As such, the example here is shown only for the sake of illustrating how to program a CLR trigger, since it represents something that should be done in a T-SQL trigger in a production environment.
An example in which a trigger (either CLR or T-SQL) can be helpful is enforcement of business rules that don’t fit neatly into CHECK constraints. For instance, a DBA might want to define a rule that any new rows inserted into the Human Resources.Department table must be inserted with a Modified Date falling in 2005. A constraint checking the ModifiedDate column would preclude any existing rows from having a date falling in that year; a trigger can be set up to operate only on newly inserted rows and is therefore a better way to enforce the rule.
The rules for this trigger will be simple: if any rows are inserted with a ModifiedDate not falling in 2005, the transaction should be rolled back, and an error should be raised. Otherwise, nothing should happen, and the transaction should be allowed to commit.
Getting the number of rows with years other than 2005 will be accomplished the same way it could be in a T-SQL trigger: the rows will be selected from the INSERTED virtual table. Both INSERTED and DELETED are available from within CLR triggers, using the context connection, as follows:SqlConnection conn =
If the number of “bad” rows is greater than zero, an error should be raised. Remember from the previous chapter that raising a clean error from the CLR can be tricky: it requires sending a RAISERROR, but wrapping the send in a try/catch block to eliminate a second error bubbling up. Finally, the transaction will be rolled back using the Transaction object. The code to do this follows:if (numBadRows > 0)
Note that to use the System.Transactions namespace, a reference to the assembly must be added. To add the reference, right-click References in Solution Explorer, click Add Reference, and select System.Transactions in the Component Name column.
The complete code for the ValidateYear trigger follows:using System;
Managing User-Defined Triggers
If an assembly has been loaded into the database using CREATE ASSEMBLY, triggers can be created ordropped without using the Visual Studio deployment task.
To create a trigger that is exposed in an assembly, use CREATE TRIGGER and specify the name of the assembly, the name of the class in which the trigger resides, and the name of the method that defines the trigger. The following code creates the ValidateYear trigger, from an assembly called UserDefinedTriggers, containing a class called Triggers:CREATE TRIGGER ValidateYear
To drop a trigger, use DROP TRIGGER. The following code drops the ValidateYear trigger:DROP TRIGGER ValidateYear
The sys.triggers catalog view contains information about both T-SQL and CLR triggers. To get information about CLR triggers, filter the type column for the value TA.SELECT *
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.