Trigger Specifics

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.OrderHeader
  AFTER DELETE
  AS
  BEGIN
  SET NOCOUNT ON
  
  -- collect OrderItems 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

---

The previous code example is much more important than 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

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