Designing and Modifying Triggers

Modifying Triggers

Earlier in this chapter, you saw details of the syntax of a Transact-SQL statement for creating triggers. Triggers can be modified using the Alter Trigger statement. Since the features of the Alter Trigger and Create Trigger statements are identical, we will not explore the syntax a second time.

It is much better to use the Alter Trigger statement to modify a trigger than to drop and then re-create the trigger. During the period between dropping and creating a trigger, a user might make a change to the table, the consequence of which is that the rules that are usually enforced by the trigger will not be enforced.

NOTE

---

Keep in mind that the order of execution is lost when the trigger is altered—you must reset it using sp_SetTriggerOrder.

Renaming Triggers

Triggers are often renamed using Transact-SQL statements designed for the creation and modification of triggers, such as Alter Trigger. As with all other database objects, a trigger can be forced to change its name using the following system stored procedure:

Exec sp_rename 'Orders_Trigger1', 'trOrders_IU'

The first parameter is the current name of the database object, and the second parameter is the new name of the object.

Disabling DML Triggers

It is possible to temporarily disable and enable triggers without dropping them:

Alter Table [OrderHeader] Disable Trigger trOrders_IU

After the execution of this statement, the specified trigger will not fire, but it will still be associated with the table. This technique is often used to load large amounts of data into a table without initiating the business logic encapsulated in a trigger.

An alternative statement has been added to SQL Server 2005 (to make it compatible with statements for DDL triggers):

DISABLE TRIGGER dbo. trOrderHeader_IU ON dbo.OrderHeader

Disabling DDL Triggers

Disable Trigger statements for DDL triggers contain a reference to trigger scope (Database or All Server):

DISABLE TRIGGER trdAuditLoginCreation ON All Server

Trigger Design Recommendations

Since triggers are relatively complex database objects, it is easy to create design, performance, or maintainability problems inside your database. Therefore, I will spend some time pointing out a proper way to use them.

Go Out ASAP

Triggers take time to execute. If your server is very busy and/or other users are locking resources in the database, execution might take much more time than expected. On the other hand, locks that you (or rather SQL Server) have placed in the database while the trigger is executing will not be released until the trigger is finished. Thus, your trigger may increase competition for resources and affect other users and their sessions.

For these reasons, you should always try to exit a trigger as soon as possible. For example, you could start (almost) every DML trigger with the following test:

If @@rowcount = 0
     Return

It will abort further execution of the trigger if no records were changed.

Keep in mind that this If clause must occur at the very beginning of the trigger. If you put it after any other statement, @@rowcount will return the number of records affected by that statement. For example, if you put a simple Print statement at the beginning of the trigger and then this test, the remainder of the trigger will not be executed:

Alter Trigger trOrderStatus_U
On dbo.[OrderHeader]
After Update    -- For Update
As

Print 'Start of trOrderStatus_U'
If @@Rowcount = 0  -- This is always true
                   -- and the rest will NEVER be executed.
     Return

      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

More Trigger Design Recommendations

Make It Simple

It is true that DML triggers are suitable for implementing complex business rules, particularly if those business rules are too complex to be handled by simpler database objects such as constraints. However, just because you are using them to handle complex business rules, you do not have to make your code so complex that it is difficult to understand and follow. It is challenging enough to work with DML triggers; keep them as simple as possible.

Divide and Conquer

In early versions of Microsoft SQL Server, only one After trigger per modification statement could be associated with a table. This physical restriction led developers to produce poor (very complex) code. Features that were not related had to be piled up in a single After trigger. However, this restriction no longer applies. There is no reason to couple the code for multiple After triggers. Each distinct piece of functionality can be implemented in a separate trigger (except in the case of Instead-of triggers).

Do Not Use Select and Print Inside a Trigger

The Print and Select commands are very useful in triggers during the debugging process. However, they can be very dangerous if left in a trigger after it has been introduced into production. These statements generate additional result sets, which might cause the client application to fail if it is not able to handle them or does not expect them.

Do Not Use Triggers at All

If you can implement the required functionality using constraints, do not use triggers! If you can implement the required functionality using stored procedures, and if you can prevent users from accessing your tables directly, do not use triggers!

Triggers are more difficult to implement, debug, and manage. You will save both time and money for your company or your client if you can find simpler ways to implement the required functionality.

Transaction Management in Triggers

A trigger is always part of the transaction that initiates it. That transaction can be explicit (when SQL Server has executed Begin Transaction). It can also be implicit—basically, SQL Server treats each Transact-SQL statement as a separate transaction that will either succeed completely or fail completely.

It is possible to abort the entire transaction from inside the trigger by using Rollback Transaction. This command, shown in action next, is valid for both implicit and explicit transactions:

Alter Trigger trOrderStatus_U
On dbo.[Order]
After Update    --For Update
As

     If @@Rowcount = 0
          Return

     If Update (OrderStatusId)
     Begin

          Insert into ActivityLog
                 ( Activity,
                 LogDate,
                 UserName,
                 Note)
          Select
'Order.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

      If @@Error <> 0
      Begin
           RAISERROR ("Error in trOrderStatus_U", 16, 1)
           Rollback Transaction
      End
End

In this trigger, SQL Server investigates the presence of the error and rolls back the complete operation if it is unable to log changes to the ActivityLog table.

The processing of Rollback Transaction inside a trigger differs from its processing inside a stored procedure. It also differs in different versions of Microsoft SQL Server.

When a Rollback statement is encountered in a stored procedure, changes made since the last Begin Transaction are rolled back, but the processing continues.

In Microsoft SQL Server 2005, when a Rollback statement is executed within a trigger, a complete batch is aborted and all changes are rolled back. SQL Server continues to process from the beginning of the next batch (or stops if the next batch does not exist).

Microsoft SQL Server 2000, 4.2, and 7.0, and all versions of Sybase SQL Server behaved in this manner. In Microsoft SQL Server 6.0, execution was continued through the trigger, but the batch was canceled. Version 6.5 went to an opposite extreme; execution of both the trigger and the batch was continued. It was the responsibility of the developer to detect an error and stop further processing.

NOTE

---

Statements in the trigger after the Rollback statement will be executed normally. Any modifications done after the rollback point will not be rolled back. However, nested triggers will not be fired as a result of modifications after the rollback point.

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