Troubleshooting Triggers

• Troubleshoot and optimize programming objects. Objects include triggers.

When working with triggers, you might find from time to time that they appear to stop functioning. This section provides you with guidance on where to start your troubleshooting efforts. It includes information on what can cause your triggers to not execute, and then moves into viewing the trigger definition. The trigger definition is useful when you want to know what the trigger was supposed to do in the first place.

Most problems that arise from triggers have to do with either execution being prevented or improper code or logic within the trigger. If the problem has to do with the code, you have to examine the trigger code and correct the problem. This section shows you how to access the code. To solve problems with the code, read Chapter 9, which was devoted to SQL Server programming techniques.

What Prevents Triggers from Executing

Triggers might not execute for a number of reasons. Here is a list of some of the common reasons and things that you might do to avoid problems and ensure that your triggers function properly:

• Actions that insert non-logged data into the table when the database is set to use the Bulk-Logged or Simple recovery model.

• Loading data using the BCP bypasses triggers, CHECK constraints, and rules.

• The TRUNCATE TABLE command does not fire DELETE triggers.

• Direct recursion does not occur if the database option of Recursive Triggers is set to OFF . This prevents triggers from firing multiple times.

• If the server option of Nested Triggers is set to '0' , then indirect recursion is also prevented. This prevents triggers from firing multiple times.

NOTE

Bulk Inserts and SQL Server 2000 By default bulk copies and bulk inserts are not logged or are minimally logged. If you specify the FIRE_ TRIGGERS option for the BULK INSERT command or the BCP utility, you will cause the entire batch to be treated as a multi-row insert into your table, and triggers will be fired . This option also becomes a fully logged operation.

If you avoid the items on this list, then your triggers should fire when you want them to. If the trigger fires, and just does not do what you want it to, then you have to examine the actual code.

Viewing a Trigger

Viewing the definition of a trigger is not a difficult task in SQL Server 2000. You can view the name , date created, and the types of triggers on a table. You might also want to know the complete definition of a trigger to help implement a new trigger design. You might also want to view the definition to find out why the trigger is not functioning properly. It might be that the wrong type of trigger was created, or there is a flaw in the programming logic.

Viewing the definition is possible, provided that the definition was not encrypted with the WITH ENCRYPTION option. Use the sp_helptrigger , sp_helptext , and sp_help stored procedures to view information about the trigger. You can use sp_helptrigger to view the types of triggers on a table, sp_help to see information about a trigger such as date created, and sp_helptext to view the trigger definition itself. The syntax for all three is as follows :

 sp_helptrigger [ @tabname = ] 'table'     [, [ @triggertype = ] 'type' ] sp_help [ [ @objname = ] 'name' ] sp_helptext [ @objname = ] 'name' 

For sp_helptrigger , 'table' is the name of the table on which the triggers are set, and 'type' specifies the type of trigger to return information about and is either INSERT , DELETE , or UPDATE . For the other two stored procedures, 'name' is the name of the trigger that you want to view. To view trigger-specific information, follow Step by Step 8.6.

STEP BY STEP

8.6 Viewing Trigger Information with Both Query Analyzer and Enterprise Manager

1. Open the SQL Server Query Analyzer by selecting it from the SQL Server 2000 group under Programs on the Start menu. Open a connection to your server and select the Northwind database.

2. To view a specific type of trigger on a table, such as UPDATE , use sp_helptrigger , as follows:

    sp_helptrigger 'Order Details', 'INSERT' GO 

3. To view trigger information, use the sp_help stored procedure, as follows:

    sp_help 'PriceCheck' GO 

4. To view the trigger definition, use the sp_helptext stored procedure, as follows:

    sp_helptext 'PriceCheck' GO 

5. Execute these stored procedures and you should get something looking like Figure 8.12.

   Figure 8.12. Viewing trigger-specific information.

   

6. Close the Query Analyzer and open Enterprise Manager from the SQL Server 2000 group under Programs on the Start menu.

7. To view a trigger on the Order Details table, expand the Console Root through your SQL Server Group, your Server Instance, Databases folder, Northwind database, and select Tables. Right-click on the Order Details table and select All Tasks, Manage Triggers.

8. After you have found the Manage Trigger dialog box, select PriceCheck from the Name drop-down menu. You can now see the contents of this trigger in the open dialog window.

9. Close the dialog box using the Cancel button, and then exit Enterprise Manager.

After viewing the definition of the trigger, as long as it was not encrypted, you should be able to review it and identify any errors that are related to the trigger. If there are problems with the trigger, you might then have to issue an ALTER TRIGGER statement.

You should now have a good feeling about what can prevent triggers from firing and how to deal with those issues by examining the trigger definition, and then making any necessary corrections to its syntax.