quotation

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.