Chapter 9: Triggers | Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET

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:

Modification Statement	Deleted	Inserted
Insert	N/A	New records
Update	Old version of updated records	New version of updated records
Delete	Deleted records	N/A

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.

Handling Changes on Multiple Records

The following example is a trigger designed to record the name of the user that changed the status of an order in the ActivityLog table, along with some additional information:

 Create Trigger dbo.trOrderStatus_U_1 On dbo.OrderHeader After Update    -- For Update As        declare @intOldOrderStatusId int,                @intNewOrderStatusId int      If Update (OrderStatusId)      Begin      select @intOldOrderStatusId = OrderStatusId from deleted      select @intNewOrderStatusId = OrderStatusId from inserted      Insert into dbo.ActivityLog( Activity,                                   LogDate,                                   UserName,                                   Note)      values ( 'OrderHeader.OrderStatusId',               GetDate() ,               User_Name(),               'Value changed from '               + Cast( @intOldOrderStatusId as varchar)               + ' to '               + Cast((@intNewOrderStatusId) as varchar)              )  End

This method is far from perfect. Can you detect the problem? It records the user who has changed the status of an order only when the user changes no more than a single order:

 select @intOldOrderStatusId = OrderStatusId from deleted

Let me remind you that if the Select statement returns more than one record, the variable(s) will be filled with values from the last record. This is sometimes all that is required. If you have restricted access to the table and the only way to change the status is through a stored procedure (which allows only one record to be modified at a time), then this is sufficient.

Unfortunately, there is always a way to work around any restriction and possibly issue an Update statement that will change the status of all tables. The following is the proper solution:

 Alter Trigger dbo.trOrderStatus_U On dbo.OrderHeader After Update -- For Update As      If Update (OrderStatusId}      begin          Insert into ActivityLog( Activity,                                   LogDate,                                   UserName,                                   Note)          Select   'OrderHeader.OrderStatusId',                   GetDate() ,                   User_Name(),                   'Value changed from '                   + Cast( d.OrderStatusId as varchar)                   + ' to '                   + Cast( i.OrderStatusId as varchar)         from deleted d inner join inserted i         on d.OrderId = i.OrderId    end

In this case, a set operation is used and one or more records from the Deleted and Inserted virtual tables will be recorded in ActivityLog.

Nested and Recursive Triggers

A trigger can fire other triggers on the same or other tables when it inserts, updates, or deletes records in them. This technique is called nesting triggers.

If a trigger changes records in its own table, it can fire another instance of itself. Such an invocation is called direct invocation of recursive triggers.

There is another scenario in which invocation of recursive triggers might occur. The trigger on one table might fire a trigger on a second table. The trigger on the second table might change the first table again, and the first trigger will fire again. This scenario is called indirect invocation of recursive triggers.

All these scenarios might be ideal for implementing referential integrity and business rules, but they might also be too complicated to design, understand, and manage. If you are not careful, the first trigger might call the second, then the second might call the first, then the first the second, and so on.

Very often, the SQL Server environment is configured to prevent this kind of behavior. To disable nested triggers and recursive triggers, you need to use the stored procedure sp_configure to set the Nested Triggers server option, and then use the Alter Table statement to set the Recursive_Triggers option to Off mode. Keep in mind that recursive triggers will be disabled automatically if you disable nested triggers.

After Trigger Restrictions

The following are the trigger restrictions, none of which usually causes any difficulties:

The trigger must be created with the first statement in a batch.
The name of the trigger is its Transact-SQL identifier, and therefore must be no more than 128 characters long.
The trigger's name must be unique in the database.
A trigger can only be associated with one table, but one table can have many triggers. In the past, only one trigger could be associated with one modification statement on one table. Now, each required function can be implemented in a separate trigger. By implementing these features in separate triggers, you assure that the triggers will be easier to understand and manage.
Triggers cannot be nested to more than 32 levels, nor can they be invoked recursively more than 32 times. Attempting to do so causes SQL Server to return an error.

A trigger must not contain any of the following Transact-SQL statements:

Alter Database	Drop Database
Alter Procedure	Drop Default
Alter Table	Drop Index
Alter Trigger	Drop Procedure
Alter View	Drop Rule
Create Database	Drop Table
Create Default	Drop Trigger
Create Index	Drop View
Create Procedure	Grant
Create Rule	Load Database
Create Schema	Load Log
Create Table	Reconfigure
Create Trigger	Restore Database
Create View	Restore Log
Deny	Revoke
Disk Init	Truncate Table
Disk Resize	Update Statistics

You cannot reference text, Ntext, or image columns in triggers, but there are no problems with referencing varchar (max), Nvarchar (max), and varbinary (max) columns:

 CREATE TRIGGER dbo.trOrderHeader_D ON dbo.Order-Header AFTER DELETE AS      BEGIN      SET NOCOUNT ON      -- collect Orderltems and Orders in OrderDeleted      INSERT INTO [dbo].[OrderDeleted]          ( [OrderId] , [OrderDate] , [RequestedById]          ,[TargetDate],[CompletionDate],[DestinationLocationId]          ,[Note],[OrderTypeId],[OrderStatusid]          , [UserName] , [ChangeDT])      SELECT [OrderId],[OrderDate],[RequestedById]         , [TargetDate] , [CompletionDate] , [DestinationLocationId]         ,[Note],[OrderTypeId],[OrderStatusid]         , SUSER_SNAME(}, GETDATE(}      FROM deleted      END

Instead-of Triggers

Instead-of triggers are executed instead of the modification statement that has initiated them. The following trigger is executed when an attempt is made to delete records from the MyEquipment table. It will report an error instead of allowing the deletion:

 Create Trigger dbo.itrMyEquipment_D On dbo.MyEquipment instead of Delete As      -- deletion in this table is not allowed      raiserror('Deletion of records in MyEquipment table is not allowed', 16, 1} GO

Instead-of triggers are executed after changes to base tables occur in Inserted and Deleted virtual tables, but before any change to the base tables is executed. Therefore, the trigger can use information in the Inserted and Deleted tables. In the following example, a trigger tests whether some of the records that would have been deleted are in use in the Equipment table:

 Create Trigger itrEqType_D On dbo.EqType instead of Delete As If exists(select *      from Equipment      where EqTypeId in (select EqTypeId                         from deleted)      )      raiserror('Some recs in EqType are in use in Equipment table!',                16, 1} else      delete EqType      where EqTypeId in (select EqTypeId from deleted) GO

Instead-of triggers are initiated before any constraints. This behavior is very different from that of After triggers. Therefore, the code for an Instead-of trigger must perform all checking and processing that would normally be performed by constraints.

Usually, an Instead-of trigger executes the modification statement (Insert, Update, or Delete) that initiates it. The modification statement does not initiate the trigger again. If some After triggers and/or constraints are defined on the table or view, they will be executed as though the Instead-of trigger does not exist.

A table or a view can have only one Instead-of trigger (and more than one After trigger) per modification type.

Triggers on Views

Instead-of triggers can be defined on views also. In the following example, a trigger is created on a view that displays fields from two tables:

 Create View dbo.vEquipment AS Select Equipment.EqId,        Equipment.Make,        Equipment.Model,        EqType.EqType From dbo.Equipment Equipment Inner Join dbo.EqType EqType On Equipment.EqTypeId = EqType.EqTypeId Go Create Trigger itr_vEquipment_I On dbo.vEquipment instead of Insert As -- If the EqType is new, insert it If exists(select EqType           from inserted           where EqType not in (select EqType                                  from EqType))       -- we need to insert the new ones       insert into EqType(EqType)           select EqType           from inserted           where EqType not in (select EqType                                from EqType) -- now you can insert new equipment Insert into Equipment(Make, Model, EqTypeId) Select inserted.Make, inserted.Model, EqType.EqTypeId From inserted Inner Join EqType On inserted.EqType = EqType.EqType GO Insert Into vEquipment(EqId, Make, Model, EqType) Values (-777, 'Microsoft', 'Natural Keyboard', 'keyboard')

The trigger first examines whether the Inserted table contains EqType values that do not exist in EqTable. If they exist, they will be inserted in the EqType table. At the end, values from the Inserted table are added to the Equipment table.

The previous example illustrates one unusual feature in the use of Instead-of triggers on views. Since EqId is referenced by the view, it can (and must) be specified by the modification statement (Insert statement). The trigger can (and will) ignore the specified value since it is inserted automatically (EqId is an identity field in the base table). The reason for this behavior is that the Inserted and Deleted tables have different structures from the base tables on which the view is based. They have the same structure as the Select statement inside the view.

Columns in the view can be nullable or not nullable. The column is nullable if its expression in the Select list of the view satisfies one of the following criteria:

The view column references a base table column that is nullable.
The view column expression uses arithmetic operators or functions.

If the column does not allow nulls, an Insert statement must provide a value for it. This is the reason a value for the EqId column was needed in the previous example.

An Update statement must provide values for all non-nullable columns referenced by the Set clause in a view with an Instead-of update trigger.

Note

You must specify values even for view columns that are mapped to timestamp, Identity, or computed-base table columns.

You can use the AllowNull property of the ColumnProperty() function (table function) to examine which fields are nullable from code.

Note

Tbe previous code example is mucb more important tban you might think. It allows you to insert a whole set of records at one time into the view (actually to the set of base tables behind the view). Before Instead-of triggers, you had to do this record by record with a stored procedure. This capability is very useful for loading information into a SQL Server database. For example, you can load information from a denormalized source (such as a flat file) and store it in a set of normalized, linked tables.

Another unusual feature of Instead-of triggers is the fact that they support text, ntext, and image columns in Inserted and Deleted tables. After triggers cannot handle these data types. In base tables, text, ntext, and image columns actually contain pointers to the pages holding data. In Inserted and Deleted tables, text, ntext, and image columns are stored as continuous strings within each row. No pointers are stored in these tables, and therefore the use of the Textptr() and Textvalid() functions and the Readtext, Updatetext, and Writetext statements is not permitted. All other uses are valid, such as references in the Select list or Where clause, or the use of Charindex(), Patindex(), or Substring() functions.

However, you can always use new varchar(max), Nvarchar(max), and varbinary(max) instead of old BLOB fields:

 CREATE TRIGGER itrOrder_D ON dbo.OrderHeader INSTEAD OF DELETE AS BEGIN SET NOCOUNT ON -- collect deleted Orders in OrderDeleted INSERT INTO [dbo].[OrderDeleted]      ([OrderId],[OrderDate],[RequestedById]      ,[TargetDate],[CompletionDate],[DestinationLocationId]      ,[Note],[OrderTypeId],[OrderStatusid]      ,[UserName],[ChangeDT]) SELECT [OrderId],[OrderDate],[RequestedById]      ,[TargetDate],[CompletionDate],[DestinationLocationId]      ,[Note],[OrderTypeId],[OrderStatusid]      , SUSER_SNAME(}, GETDATE() FROM deleted delete dbo.[OrderHeader] where OrderId in (select OrderId from deleted) END GO

DML Trigger Order of Execution

SQL Server 7.0 introduced the idea that more than one trigger could be created per modification statement. However, the execution order of such triggers could not be controlled. In SQL Server 2000 and SQL Server 2005, it is possible to define which DML After trigger to execute first and which to execute last against a table. For example, the following statement will set trlnventory_l to be the first trigger to be executed in the case of an Insert modification statement:

 Exec sp_settriggerorder @triggername = 'trInventory_I',                         @order = 'first',                         @stmttype = 'INSERT'

The @order parameter must have one of these values: 'first', 'last', or 'none'. The value 'none' is used to reset the order of the execution of the trigger after it has been specified. The @stmttype parameter must have one of these values: 'INSERT', 'UPDATE', or 'DELETE'.

Since only one Instead-of trigger can be associated with a table, and since it is executed before any other trigger (or constraint), it is not possible to set its order.

Note

Alter trigger statements reset the order of the trigger. After altering the trigger, you must execute the sp_SetTriggerOrder statement to set it again.