Alternative Coding MechanismsTriggers 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.Chapter 8, "Designing for Optimized Data Access," and Chapter 9, "Designing a Database Security Plan." Code Responses to ActionsTriggers 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.FOREIGN KEY constraints. In SQL Server 2000, it is far more efficient to use cascading actions. |
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:
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)
ALTER DATABASE | ALTER TABLE | CREATE |
DISK | DROP | GRANT |
LOAD DATABASE | LOAD TRANSACTION | RECONFIGURE |
RESTORE DATABASE | REVOKE | SELECT INTO |
UPDATE STATISTICS |
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.