The Trigger Plan

Like the integrity plan described in the preceding chapter, a trigger plan is another important section of your overall database definition, model, or architecture. A trigger plan should also reflect the stages of trigger deployment and provide a checklist to ensure that all factors affecting triggers and affected by triggers have been taken into consideration. You can use the flow diagram in Figure 13–1 to build your trigger plan.

Figure 13–1: The trigger plan

Gather Business Rules and Motivation for Triggers

Identify the business rules that can be best addressed by triggers. This section of the database plan identifies business rules that will impact the database model and architecture. Sit down with the people responsible for establishing the business rules and determine how best to cater to their needs using triggers.

Identify Key Trigger Issues to Cover

Issues to be catered to by triggers must be referenced or become part of the database architecture. If you are the DBA but do not write triggers, or if you are assigning the trigger writing to SQL Server developers or third parties, then it is important to list key issues that will impact the development plan. For example, note how you plan to maintain triggers. While most trigger code is straight up and down, a need may arise for the creation of a complex trigger, and the code needs to be documented and maintained and protected like all source code. Trigger code can also be dangerous if it falls into the wrong hands. And if you decide that encrypting the code is an option, then you need to be sure the source code is stored in a backup system, in documentation, or in source files that are secured. (I personally do not like encrypting objects like triggers and stored procedures. Some DBAs encrypt to protect the system against malicious or careless individuals who may have rights in the database. This is simply poor management.)

List the Triggers and Their Objectives

In this section, list the precise objective of each trigger you need to create. As with constraints and stored procedures, an extensive system of triggers can hold a lot of trigger objects, and it will not take long for you to lose track of what triggers are installed where and for what reason. This problem is further compounded by an order of magnitude when more than one trigger is installed or defined for a table, or when you create a trigger cascade or nest.

Fully Document the Solution for Each Trigger

Once you have written a trigger or a collection of triggers, then each trigger, including the actual trigger code and the trigger’s relation to other triggers, constraints, checks, and stored procs, should be fully documented. This should not only help you see the trigger trees for the trigger forest but help you pinpoint areas that could use improvement.

Most important, trigger documentation-both externally in supporting documents and internally using inline comments-can help other parties read your code. The documentation will also help you debug problems. Remember that triggers are regarded as transactions, so documenting them as such will allow you to debug and recover from problems created by rogue triggers. It is not normal to have a trigger go nuts on you and do substantial damage, but when I started out writing triggers, I once created a cascading delete that cascaded my entire database down the drain. To this day I am not sure how it happened, but it did.

In addition to documenting the solution arrived at for the trigger, it is also imperative to fully document the progress being made on the trigger or triggers under assignment. Some triggers may be extensive, and many may require the trigger writer to be supervised over several weeks or even on a permanent basis. To put it in more direct terms, you should not regard the management of a trigger or T-SQL project, in terms of managing the software creation process, as any different from a C# or Visual Basic 2005 project.

Do Cost Analysis

There are also costs attached to every trigger. As in the case of constraints, the costs are both direct, in terms of their consumption of SQL Server resources and usefulness, and indirect, in terms of the costs of programming, maintenance, documentation, and so on.

As mentioned in connection with the integrity plan, triggers are procedural and their code cannot be guaranteed by SQL Server to be ANSI-compliant or even safe. SQL Server can check code for errors, but it is not able to check trigger code for standards compliance on behalf of the trigger author or warn you that you are about to take unpaid permanent leave from your very fine DBA job.

Cost analysis of triggers should also cater to resource costs, and you should thus thor oughly test trigger code (in execution plans) and in the profiler (see Chapters 15 and 16).

Determine Error Checking Requirements

Determine a system for checking errors that materialize from trigger code. Errors can be directly caused by triggers, or they can result from the correct trigger execution with unpredictable or unintended results. Investigate the feasibility of deploying an error database, something like a bug recording system that records error messages that derive from both triggers and stored procedures. If you follow the advice dispensed in Chapter 12 and move either all or a substantial part of the processing to SQL Server, this is an essential practice.

You can also set up logging in the profiler and enable a security system to track who does what and when they did it. But the profiler does not cater to errors that occur in T-SQL code and that create problems with the database or the data.

Determine Effects in the ClientTier

Understanding the effects in the client tier or what reporting and alerting is required at the client is important. What we do in the data tier can affect the client tier, especially if the client processes have been implemented in a middle tier, especially in Web services. If you still need to maintain legacy client code, which means client-side data processing and logic, trigger implementation and especially stored procedure implementation can have unpredictable results, and these need to be considered.