Triggers


A trigger is a special kind of stored procedure invoked when a table is modified (for example, when a row is inserted, updated, or deleted). Triggers are associated with tables and the action that should activate them (for example, on insert/update/delete of rows).

With triggers, changes of rows can be cascaded through related tables or more complex data integrity can be enforced.

Within a trigger you have access to the current data of a row and the original data, so it is possible to reset the change to the earlier state. Triggers are automatically associated with the same transaction as the command that fires the trigger, so you get a correct transactional behavior.

The trigger CategoryDelete that follows can be used with the Northwind database. This trigger is fired when a row in the Categories table is deleted. If a category is deleted, with all products from the Products table that have the same CategoryId, the column Discontinued is set to 1.

Of course, this trigger can only be used if there is no constraint between the Category and Products tables that checks for referential integrity:

 CREATE TRIGGER CategoryDelete ON Categories FOR DELETE AS UPDATE Products SET Discontinued = 1 FROM Products INNER JOIN Deleted ON Products.CategoryId = Deleted.CategoryId 

Creating Triggers

The example shown here demonstrates implementing data integrity with triggers when new records are inserted to the Users table. To create a trigger with the CLR, a simple class must be defined that includes static methods that have the attribute [SqlTrigger] applied. The attribute [SqlTrigger] defines the table that is associated with the trigger and the event when the trigger should occur. In the example, the associated table is Users which is indicated by the Target property. The Event property defines when the trigger should occur; here the event string is set to FOR INSERT, which means the trigger is started when a new row is inserted in the Users table.

The property SqlContext.TriggerContext returns the trigger context in an object of type SqlTrigger Context. The SqlTriggerContext class offers three properties: ColumnsUpdated returns a Boolean array to flag every column that was changed, EventData contains the new and the original data of an update in an XML format, and TriggerAction returns an enumeration of type TriggerAction to mark the reason for the trigger. In the example, it is compared if the TriggerAction of the trigger context is set to TriggerAction.Insert before continuing.

Triggers can access temporary tables; for example, here the table INSERTED is accessed. With INSERT, UPDATE, and DELETE SQL statements, temporary tables are created. The INSERT statement creates an INSERTED table; the DELETE statement creates a DELETED table. With the UPDATE statement both INSERTED and DELETED tables are used. The temporary tables have the same columns as the table that is associated with the trigger. The SQL statement SELECT Username, Email FROM INSERTED is used to access username and e-mail, and to check the e-mail address for a correct syntax. SqlCommand.ExecuteReader() returns a reader object that is used to read the inserted record. Username and e-mail are read from the data record. Using the regular expression class RegEx the expression used with the IsMatch() method checks if thee-mail address conforms to valid e-mail syntax. If it doesn't conform, an exception is thrown and the record is not inserted, because a rollback occurs with the transaction:

 using System; using System.Data; using System.Data.Sql; using Microsoft.SqlServer.Server; using System.Data.SqlClient; using System.Data.SqlTypes; using System.Text.RegularExpressions; public partial class Triggers { [SqlTrigger(Name ="InsertUser", Target="Users", Event="FOR INSERT")] public static void InsertUserRegistration() { SqlTriggerContext triggerContext = SqlContext.TriggerContext; if (triggerContext.TriggerAction == TriggerAction.Insert) { SqlConnection connection = new SqlConnection("Context Connection=true"); SqlCommand command = new SqlCommand(); command.Connection = connection; command.CommandText = "SELECT Username, Email FROM INSERTED"; connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.Close Connection); reader.Read(); string username = (string)reader["Username"]; string email = (string)reader["Email"`]; reader.Close(); if (!Regex.IsMatch(email, @"^.+@[*\.[a@@hyz]{2,}$")) { throw new Exception("Invalid email"); } } } } 

Using Triggers

Using deployment of Visual Studio 2005, the trigger can be deployed to the database. You can use the CREATE TRIGGER command to create the trigger manually:

 CREATE TRIGGER InsertUser ON Users FOR INSERT AS EXTERNAL NAME Demo.UserRegistrationTriggers.InsertUser 

Trying to insert rows to the Users table with an incorrect e-mail throws an exception, and the insert is not done.




Professional C# 2005
Pro Visual C++ 2005 for C# Developers
ISBN: 1590596080
EAN: 2147483647
Year: 2005
Pages: 351
Authors: Dean C. Wills

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net