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 uCreditCard that follows is part of the AdventureWorks sample database. This trigger is fired when a row in the CreditCard table is updated. With this trigger the ModifiedDate column of the CreditCard table is updated to the current date. For accessing the data that is changed, the temporary table inserted is used.

  CREATE TRIGGER [Sales].[uCreditCard] ON [Sales].[CreditCard] AFTER UPDATE NOT FOR REPLICATION AS BEGIN     SET NOCOUNT ON;     UPDATE [Sales].[CreditCard]     SET [Sales].[CreditCard].[ModifiedDate] = GETDATE()     FROM inserted     WHERE inserted.[CreditCardID] = [Sales].[CreditCard].[CreditCardID]; END; 

Creating Triggers

The example shown here demonstrates implementing data integrity with triggers when new records are inserted into 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 SqlTriggerContext. 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 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.ExecuteRow() returns a row represented in a SqlDataRecord. 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 the e-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 ="InsertContact", Target="Person.Contact", Event="FOR INSERT")]    public static void InsertContact()    {       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 EmailAddress FROM INSERTED";          connection.Open();          string email = (string)command.ExecuteScalar();          connection.Close();          if (!Regex.IsMatch(email,                @"([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$"))          {             throw new FormatException("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 InsertContact ON Person.Contact FOR INSERT AS EXTERNAL NAME Demo.Triggers.InsertContact 

Tip 

A bug in Visual Studio 2005 prevents the attribute [SqlTrigger] from being used for tables not in the dbo schema (for example, the table Person.Contact in the Person schema). Instead, you can use the CREATE TRIGGER command.

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