Triggers

Triggers are a unique type of procedure. They are very similar to events—a type of procedure in certain programming languages such as Visual Basic. Events in Visual Basic are initiated by the system when certain actions occur (for instance, a form is loaded, a text box receives focus, or a key is pressed). Triggers are associated with SQL Server objects (such as tables) and executed by SQL Server when a specific change occurs.

In the following sections, we’ll first examine conceptually different types of triggers: the classic (After) triggers, the Instead-of triggers, and finally DDL triggers. In the remaining sections, we’ll discuss methods for managing triggers from Transact-SQL code and in Management Studio, and then finally discuss trigger design recommendations.

DML Triggers

SQL Server 7.0 and earlier versions recognized only one type of trigger. In SQL Server 2000 and SQL Server 2005, this type is called an After trigger. SQL Server 2000 introduced a new type—the Instead-of trigger. Both types are fired on the same types of modification statements:

• Insert
  
• Update
  
• Delete

Since these statements are considered Data Modification Language (DML), After triggers and Instead-of triggers are jointly called DML triggers.

Physical Design of After Triggers

The following is the simplified syntax for implementing the core functionality of After triggers:

Create Trigger trigger_name
On table
{After { [Delete] [,] [Insert] [,] [Update] }
  As
    sql_statement [...n]

As a stored procedure, a trigger logically consists of

• A header, which is a Transact-SQL statement for creating a trigger. It consists of three components:
  
  • The name of the trigger
    
  • The name of the table with which the trigger will be associated
    
  • A modification statement (that is, an event) that will initiate the trigger
    
• A body, which contains Transact-SQL statement(s) to be executed at runtime.

The following example first creates a new table called MyEquipment, then populates it with Make and Model information from the Equipment table, and finally creates a trigger. The trigger is named trMyEquipment_D and is associated with the MyEquipment table. It is fired after a Delete statement is executed against the table. Its function is very simple—it notifies the user regarding actions and the number of records that have been deleted.

Create Table dbo.MyEquipment
     (Id int identity,
     Description varchar(500))
GO

-- populate table
Insert dbo.MyEquipment(Description)
     Select top 5 Make + ' ' + Model from dbo.Equipment
GO

Create Trigger dbo.trMyEquipment_D
On dbo.MyEquipment
After Delete  -- For Delete
As
     Print 'You have just deleted '
          + Cast(@@rowcount as varchar)
          + ' record(s)!'
Go

To execute the trigger, you need to execute the Delete statement:

Delete dbo.MyEquipment
Where Id = 2

SQL Server returns the following:

You have just deleted 1 record(s)!

(1 row(s) affected)

You can also execute the Delete statement to delete multiple records:

Delete dbo.MyEquipment

Even in this case, the trigger will not be fired once for each record. You will receive just one message:

You have just deleted 4 record(s)!

(4 row(s) affected)

For this reason, it is important to design your trigger to handle actions against multiple records. You will see more reasons in following paragraphs.

Inserted and Deleted Virtual Tables

SQL Server maintains two temporary virtual tables during the execution of a trigger: Deleted and Inserted. These tables contain all the records inserted or deleted during the operation that fired the trigger. You can use this feature to perform additional verification or additional activities on affected records.

You are probably wondering if there is an Updated table. No. Because an Update can be performed as a combination of the Delete and Insert statements, records that were updated will appear in both the Deleted and Inserted tables.

SQL Server does not create both tables in all cases. For example, in a trigger fired during a Delete statement, only a Deleted virtual table is accessible. A reference to an Inserted virtual table will cause an error.

The following table summarizes the presence of virtual tables in the relevant Transact-SQL statements:

The following modifies the trigger from the previous section to display which records are deleted:

Alter Trigger trMyEquipment_D
On dbo.MyEquipment
After Delete     -- For Delete
As
     Select 'You have just deleted following '
          + Cast(@@rowcount as varchar)
          + ' record(s)!'

     Select * from deleted
go

When you delete all records from the MyEquipment table, SQL Server returns the following:

-------------------------------------------
You have just deleted following 5 record(s)!

(1 row(s) affected)

Id          Description
----------- -------------------------------1           Toshiba Portege 7020CT
2           Sony Trinitron 17XE
3           NEC V90
4           HP LaserJet 4
5           HP LaserJet 4

(5 row(s) affected)

You can use values from these tables, but you cannot modify them directly. If you need to perform some operation on records that were inserted, for example, you should not try to change them in the Inserted table. The proper method would be to issue a regular Transact-SQL statement against the original table. In the Where or From clause, you can reference the virtual table (Inserted) and in that way limit the subset of the original table that you are targeting.

In the following example, the trigger calculates a SOUNDEX code for the Make and Model of the Equipment records affected by the Insert or Update statement that has fired the trigger:

Alter Trigger trEquipment_IU
On dbo.Equipment
After Insert, Update   -- For Insert, Update
As
     -- precalculate ModelSDX and MakeSDX field
     -- to speed up use of SOUNDEX function
     update dbo.Equipment
     Set ModelSDX = SOUNDEX(Model),
          MakeSDX = SOUNDEX(Make)
     where EqId IN (Select EqId from Inserted)

What Triggers a Trigger?

A DML trigger is executed once for each modification statement (Insert, Update, or Delete). An After trigger is fired after the modification statement finishes successfully. If a statement fails for another reason (for example, foreign key or Check constraints), the trigger is not invoked. For example, the Equipment table has the following Delete trigger:

Alter Trigger Equipment_DeleteTrigger
On dbo.Equipment
After Delete    -- For Delete
As
Print 'One or more rows are deleted in Equipment table!'

If you attempt to delete all records from the table:

delete dbo.Equipment

SQL Server aborts the execution because there is a foreign key relationship with the Inventory table. The execution is aborted before the trigger is invoked:

Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK_Inventory_EqId". The conflict occurred in database "Asset5", table "Inventory", column 'EqId'.
The statement has been terminated.

A trigger and developer might have different definitions of what is a successfully finished modification to a table. The trigger will fire even when a modification statement affected zero records. The following example is based on the assumption that the record with EqId set to 77777 does not exist in the database:

Delete dbo.Equipment
Where EqId = 77777

SQL Server nonchalantly prints from the trigger:

One or more rows are deleted in Equipment table!

Full Syntax of After Triggers

The After trigger was the only type of trigger before SQL Server 2000. After triggers in SQL Server 2000 have the same syntax as before except that the keyword For is (optionally) replaced with After:

Create Trigger trigger_name
On table
[With Encryption]
{
     {After | For { [Delete] [,] [Insert] [,] [Update] }
         [With Append]
          [Not For Replication]
         As
             sql_statement [...n]
     }
     |
     {After | For { [Insert] [,] [Update] }
         [With Append]
         [Not For Replication]
         As
         {    If Update (Column)
             [{And | Or} Update (Column)] 
                 [...n]
             | If (Columns_Updated()  
                          {bitwise_operator}
                          updated_bitmask)
                 { comparison_operator} column_bitmask [...n]
         }
             sql_statement [ ...n]
     }
}

If a trigger is defined with the With Encryption clause, SQL Server encrypts it so that its code remains concealed. Keep in mind that you need to preserve the source code in a script outside SQL Server if you plan to modify it later.

The Not For Replication clause indicates that SQL Server should not fire a trigger during replication of the table.

The With Append clause is used only when the compatibility mode of SQL Server is set to a value less than 70. For more details, refer to SQL Server Books OnLine.

It is possible to determine which columns were updated during the Update operation. Transact-SQL includes two functions that you can use within the trigger—Update() and Columns_Updated():

If Update (column)
sql_statement [ ...n]

If (Columns_Updated() {bitwise_operator} updated_bitmask)
                      {comparison_operator} column_bitmask [...n]
    sql_statement [ ...n]

You can now modify your previously used trigger to update only the fields that were changed:

Alter Trigger trEquipment_IU
On dbo.Equipment
After Insert, Update   -- For Insert, Update
As
     -- precalculate ModelSDX and MakeSDX field
     -- to speed up use of SOUNDEX function
     if Update(Model)
          update dbo.Equipment
          Set ModelSDX = SOUNDEX(Model) 
          where EqId IN (Select EqId from Inserted)

     if Update(Make)
          update dbo.Equipment
          Set MakeSDX = SOUNDEX(Make)
          where EqId IN (Select EqId from Inserted)
     go

The Update() function might not perform exactly as you expect. In fact, it returns True for columns that were referenced during the Transact-SQL statement rather than for columns that were actually changed. For example, if you issue the following Update statement, SQL Server references the Make column of all records, and the trigger recalculates the SOUNDEX code in all records:

Update dbo.Equipment
Set Make = Make

TIP

---

This behavior might cause some problems for you if you forget about it. However, in some cases, you can use it to your advantage. For example, to speed up the upload of information to the table, you can temporarily disable triggers (see the “Disabling DDL Triggers” and “Disabling DML Triggers” sections near the end of this chapter). Later, when you want to execute the triggers  (for example, to verify their validity and/or perform additional activities), you can use this feature to initiate triggers for records that are present in the table.

Too often, developers forget that the presence of a Default constraint in a column causes the Update() function to return True for that column during the execution of the Insert statement. This will occur even if the Insert statement did not reference the column itself.

The Columns_Updated() function operates with a bitmap that is related to the positions of columns. You can investigate its contents if you use an integer bitmask. To test whether the third column in a table was updated, you can use the following:

  if Columns_Updated() & 3 = 3
       print 'Column 3 was updated!'

The ampersand (&) is a binary and operator, with which you can test the value of the flag.

Naturally, hard-coding the order of columns does not make much sense. The real value of this function is as a means of looping through all the columns that were updated and performing specified actions.

The following trigger loops through columns and displays which ones were updated:

Create Trigger trEquipmentN_IU_2
-- list all columns that were changed
On dbo.EquipmentN
after Insert, Update
As

     Set Nocount Off
     declare @intCountColumn int,
             @intColumn int

     -- count columns in the table
     Select @intCountColumn = Count(Ordinal_position)
     From Information_Schema.Columns
     Where Table_Name = 'EquipmentN'
     Select Columns_Updated() "COLUMNS UPDATED"
     Select @intColumn = 1

     -- loop through columns
     while @intColumn <= @intCountColumn 
     begin
          if Columns_Updated() & @intColumn = @intColumn
               Print 'Column ('
                    +  Cast(@intColumn as varchar)
                    + ') '
                    + Col_Name(Object_ID('EquipmentN'), @intColumn)
                    + ' has been changed!'
          set @intColumn = @intColumn + 1 
     End

Use the following statement to test this trigger:

Insert EquipmentN(Make, Model, EqTypeID) Values('Acme', '9000', 1)

You will notice that unlike an Update statement, an Insert statement will make changes to all columns.

Please check back next week for the continuation of this article.