Alternative Coding Mechanisms | MCAD/MCSE/MCDBA 70-229 Exam Cram 2: Designing & Implementing Databases w/SQL Server 2000 Enterprise Edition

Triggers are sections of SQL Server programming code that execute when data modification is performed on a table in the database. Triggers offer a method of constraining data input and performing other tasks when data is added, deleted, or updated in the database. Other methods of constraining data include using constraints and rules.

Trigger and constraints handle data control differently. Constraints such as FOREIGN KEY and CHECK constraints are fast and efficient, but triggers offer more functionality. If you are looking for quick and easy, use constraints. If you need flexibility and functionality, use a trigger.

Because our current topic focuses more on coding and the implementation of T-SQL, the focus for our constraint discussions will center around triggers. We will revisit the discussion of choices based on security and performance in Chapter 8, "Designing for Optimized Data Access," and Chapter 9, "Designing a Database Security Plan."

Code Responses to Actions

Triggers perform an important role in SQL Server 2000: They enable you to control what happens when a user inserts, deletes, or updates data in the tables or views in your database. This control can be used to restrict the values that are inserted, prevent deletion of records, update related tables, store denormalized data, or log actions. Triggers might be used for these reasons and others, but many triggers are used to restrict or constrain data input when traditional constraints are not capable enough. A trigger contains code that is similar to a stored procedure, but it is automatically executed when an INSERT, DELETE, or UPDATE statement is invoked. Triggers are often used to help enforce and follow business logic for your organization.

There is no way to force a trigger to execute without performing the associated data modification on the table. Triggers can be used to enforce the business rules in a database when constraints are not sufficient. Typically, triggers are a more CPU-intensive way to implement tasks, and you are usually better off to implement restrictions through constraints, such as FOREIGN KEY or CHECK constraints, because they do not cause performance to suffer as much. Most of the performance impact that triggers suffer from occurs when they reference tables that are not in memory.

Triggers can enforce complex restrictions by raising a user-defined error using the RAISERROR() command. Triggers can be used to track or log changes to a table. More than one AFTER TRigger can be created on a table. With INSTEAD OF TRiggers, only one trigger of each type (INSERT, UPDATE, or DELETE) can be created on a table. Triggers consume relatively more performance than FOREIGN KEY constraints. If the trigger references only tables that are in memory, performance is similar. Stored procedures, both local and remote, might be executed from triggers.

When you're working with triggers, it is important to remember the order in which they execute. INSTEAD OF triggers execute instead of the attempted data modification (INSERT, UPDATE, or DELETE). The INSTEAD OF TRigger could actually proceed to perform the modification to the table itself. Constraints are applied after the INSTEAD OF trigger, and AFTER TRiggers are executed after the constraints and data modification take place.

When comparing stored procedures to triggers, and assuming that the code used is of similar complexity, both offer some advantages. Stored procedures might be less CPU intensive on data failures than AFTER TRiggers, because an AFTER trigger reverses the data modifications, whereas a stored procedure just doesn't make the data modification. Stored procedures follow the same ownership chain rules as views do. For example, if you own a table, and you own the stored procedure that performs an action on the table (such as a DELETE), then you only need to grant the EXECUTE permission to the stored procedure and do not need to grant the DELETE permission to the table. In this way, the stored procedures can offer greater data security; but when you implement a trigger, the code will be executed when the data is modified in the table, regardless of whether the stored procedure is used. This function of triggers is useful because some people are granted access directly to the table and the code will still be executed.

Many AFTER TRiggers can be specified for each INSERT, UPDATE, or DELETE action. If multiple triggers exist, you can specify the first and last trigger to fire. The others are fired in no particular order, and you cannot control that order. An AFTER trigger can be defined only on a table. Only one INSTEAD OF TRigger can be defined for each of the triggering actions; however, an INSTEAD OF TRigger can be defined on a view as well as a table.

In previous releases, you could use triggers to help enforce referential integrity constraints. This was difficult and required that you eliminate other elements, such as FOREIGN KEY constraints. In SQL Server 2000, it is far more efficient to use cascading actions.

To define a trigger, you can select the Manage Triggers option from the Table Design window. You can also go to the table to which you want to attach a trigger, and you can find the option as an extension of the pop-up menu off the All Tasks option.

You can use the T-SQL CREATE TRIGGER statement to create triggers for all applicable operations. You can access this command from the Enterprise Manager by using the Managing Triggers option. Managing Triggers in the Enterprise Manager (and in other objects as well) provides you with the shell of a CREATE statement even if you are changing an existing trigger. The Enterprise Manager enables you to change an existing trigger, first dropping the trigger before re-creation. The ALTER TRIGGER statement is used to change the definition of a trigger without dropping it first, and it is used only through T-SQL. An example of the creation of a trigger using T-SQL is as follows:

 CREATE TRIGGER UpdatedCustomer ON CustomerTable FOR INSERT, UPDATE AS             declare @phone nvarchar(20)             declare @Contact nvarchar(100)             select @phone = phoneno,                    @contact = contactname from inserted             RAISERROR(50100, 1, 1, @Contact, @Phone)

This procedure is one of my favorite implementations for use in customer applications. In the case of customer information, an automated alert that sends an email message to the salesperson could be defined around the error being raised. On an INSERT, a clerk or salesperson may make an initial client contact call based on an email that the alert may send. In the event of an UPDATE, the clerk could call the client to ensure that the new information is accurate. The benefit is that the trigger automatically fires when new rows are added to the table or changes are made to the customer information.

Triggers are powerful tools for development of a database system. Consider the following points when you are creating and using trigger functionality in a DBMS environment:

Triggers can process all three actions: UPDATE, DELETE, and INSERT.
AFTER triggers apply to a single table and can be made column-level.
AFTER TRiggers cannot be created on views or temporary tables.
INSTEAD OF TRiggers are the only triggers that can be created on views.
Triggers can be created with the SQL Server Enterprise Manager, the Query Analyzer, and programmatically through SQL-DMO.
Triggers are database objects and follow object-naming conventions.
Triggers can be created and altered by the sysadmin, db_owner, and db_ddladmin roles, as well as the table owner.
Triggers cannot use any of the following statements:
ALTER DATABASE
ALTER TABLE
CREATE
DISK
DROP
GRANT
LOAD DATABASE
LOAD TRANSACTION
RECONFIGURE
RESTORE DATABASE
REVOKE
SELECT INTO
UPDATE STATISTICS

If possible, you should avoid creating triggers for simple tasks; constraints perform the task faster and with less CPU overhead. Triggers should be used only when constraints do not offer the power or flexibility required.

After a trigger is created, you might need to make slight changes to the way it processes because policies or procedures change in your organization. This is not a problem. You can easily change the way a trigger processes, either by deleting and then re-creating the trigger, or by altering the trigger via a one-step process using ALTER TRIGGER. It is worth noting that when you change a trigger from within the Enterprise Manager, it shows you a CREATE trigger operation even though it is an ALTER that is actually used behind the scenes.

ALTER is the preferred method over dropping and re-creating because the ID in the syscomments table remains the same. If you have other scripts on your server that have been coded to look for the trigger by its object_ID, these scripts will fail if the object_ID is changed; coding your scripts with these types of dependencies is generally a bad practice. The syntax for. ALTER TRIGGER is comparable to that of the CREATE TRIGGER statement and has all the same arguments.

Triggers might also be renamed by any user who has the correct permission and by the database owner. Triggers must follow the rules for naming identifiers. You are not likely to have to rename a trigger very often, but you might end up with a naming conflict with another item you want to add to the database. If policy or procedures prevent you from being able to rename the new item, you might have to rename your trigger.

When a trigger is renamed, any references to the trigger name inside of other objects are not updated, and they have to be updated manually or they will not work. You should run sp_depends 'trigger_name' to find out where the trigger is referenced before you rename the trigger. To rename a trigger, use the sp_rename system stored procedure. In some instances you might want to delete the trigger and re-create it with a new name. The next topic is deleting or dropping triggers.

Dropping Triggers

Deleting triggers is as easy as renaming them. Deleting a trigger does not affect the underlying table or data contained in the table. To remove triggers using the SQL Server Query Analyzer, you have to use the DROP TRIGGER statement. When dropping a trigger, you should first find out whether any tables or objects reference it. To find out which objects reference a trigger, run sp_depends 'trigger_name' before you drop the trigger. If you do not check to see which objects reference the trigger, you risk having those objects fail to function. You cannot perform this check after you have deleted the trigger.

Appropriate Use of INSTEAD OF Triggers

INSTEAD OF triggers can change any data-modification statement into a customized action. INSTEAD OF triggers place all the rows of a DELETE statement that would have taken place into the Deleted table, and in the case of an INSERT statement, all the rows that would have been inserted into the Inserted table. You can then script an appropriate set of steps to validate the proposed action, before the application or violation of any constraints that might be implemented. Constraint violations roll back your last statement, whereas the INSTEAD OF trigger can test for violations and then modify the proposed action to avoid the constraint violation. If an INSTEAD OF trigger has already been executed, and the constraints are still violated, then any actions taken in the INSTEAD OF trigger are rolled back. With the INSTEAD OF INSERT trigger, the proposed update (including who attempted it) can be logged, and you can issue a rollback before the constraints are applied.

Before SQL Server 2000, the only type of trigger available was an AFTER triggera trigger that contains a set of statements that fire after a modification to a table has been made and after any applicable constraints have been applied. INSTEAD OF triggers are new to SQL Server 2000 and execute instead of the triggering action.

INSTEAD OF triggers are not fired recursively. If the INSTEAD OF INSERT TRigger fires, and then proceeds to issue the INSERT on the table, the INSTEAD OF TRigger would be skipped, constraints would be checked, and the AFTER trigger would fire. Failure at the constraints would cause the statement to be cancelled, and a rollback could be issued if the AFTER TRigger fails. A rollback would reverse all actions performed since the original statement was issued, so any actions performed by the INSTEAD OF TRigger would also be rolled back.

One of the great benefits of INSTEAD OF triggers is that they can be added to views. The INSTEAD OF TRigger is the only type of trigger that can be placed on a view. Normally, views that reference multiple tables are not updatable because changes can be made to only one table at a time. With the help of an INSTEAD OF TRigger, these views can be made to appear to update multiple tables at once. Also, views based on multiple tables using joins cannot normally have data deleted, but with an INSTEAD OF TRigger, this too can be accomplished. An INSTEAD OF trigger has access to the deleted table, so it can use this deleted information to find which underlying base table needs to have data deleted.

The following are some guidelines you should observe when creating INSTEAD OF triggers:

There can be only one INSTEAD OF trigger for each action on a table.
INSTEAD OF TRiggers are new to SQL Server 2000 and execute before the triggering statement. INSTEAD OF TRiggers are the only triggers that can be implemented on a view.
INSTEAD OF triggers are never recursively fired. That is, if an INSTEAD OF TRigger fires a custom INSERT statement to the same triggered table, the INSTEAD OF trigger will not fire again.
INSTEAD OF triggers cannot be used on tables that have cascaded updates.
INSTEAD OF triggers can reference text, ntext, and image columns in their Inserted and Deleted tables.

Trigger Firing Order

Only a few options allow you to change when a trigger is executed. One is when the trigger is created, and the other is with the sp_settriggerorder procedure. Recursion can also play a role in how triggers are fired or executed. The first thing to look at is the different types of triggers.

When you create a trigger, you create an INSTEAD OF, AFTER, or FOR trigger. Depending on the type of trigger, your trigger will execute at a different time. This is an issue that has been previously discussed in this chapter, but it is important, and it's worth covering again.

When creating a trigger, you use one of the following statements:

CREATE TRIGGER name ON table INSTEAD OF action Trigger fires before constraints are checked and before that data modification is processed.
CREATE TRIGGER name ON table AFTER action Trigger fires after the data modification and after constraints are checked. Constraints can cancel the statement, causing the trigger to never fire.
CREATE TRIGGER name ON table FOR action Trigger fires after data modification and after constraints are checked. There is no difference between FOR and AFTER. Microsoft's SQL Server includes both FOR and AFTER for backward compatibility but treats them the same way. Before SQL Server 2000, all triggers were FOR TRiggers.

In addition to the type of trigger you are working with, you can set a FIRST and LAST TRigger when you are working with AFTER triggers. Because there can be only one INSTEAD OF TRigger of each type on a table, there is no need or reason to attempt to set the fire order of INSTEAD OF triggers. However, there can be many AFTER TRiggers of each type set on a single table.

You might have multiple triggers so that you can define multiple actions to take when data in a table is modified. Rather than having multiple triggers, you can alter a trigger, making it larger and larger to accommodate all the logic. In many cases, smaller, more specific triggers can be easier to manage, code, and troubleshoot. Because there are multiple triggers of each type, you might have a reason to want one to fire before the others, and therefore want to set the order of triggers. To change the fire order of triggers, you can use the sp_settriggerorder stored procedure.

Outside of FIRST and LAST, you have no control over how triggers will fire. If you have already specified a FIRST or LAST trigger, you cannot specify another trigger as FIRST or LAST. If you want to remove a FIRST or LAST option, the ALTER TRIGGER statement always removes this setting when it is executed. If you alter a trigger and want to leave it as FIRST or LAST, you will have to reexecute the sp_settriggerorder statement.