Create and alter database objects. Objects include triggers, and how to specify schema binding and encryption settings.
This objective in general requires you to know how to create triggers using the CREATE TRIGGER statement. When creating triggers, you also need to know how to set properties such as definition encryption.
Manage data manipulation by using triggers including validate data.
This objective deals with controlling what goes in and what gets deleted in your table. You need to know how to create UPDATE , DELETE , and INSERT triggers and when to use them.
Control data access by using triggers.
This objective is linked to the previous objective on managing data manipulation. This objective looks specifically at how triggers can be used to restrict which users are able to modify the data protected by a trigger.
Enforce procedural business logic by using triggers.
Specify trigger actions.
Filter data by using triggers.
This objective looks at creating the actual contents of the trigger. This includes writing procedural code to fire when an UPDATE , DELETE , or INSERT statement is executed. Take a look at Chapter 6, "Programming SQL Server 2000," to see how to create professional triggers.
Troubleshoot and optimize programming objects. Objects include triggers.
This objective deals with altering and viewing triggers to make them more efficient. This chapter includes lots of tips to help you create efficient, working triggers.
O UTLINE
Constraining Data Modifications
Views
Stored Procedures
Defaults
Rules
Constraints
Triggers
Overview of Triggers
Creating Triggers
Altering Triggers
Renaming Triggers
Dropping Triggers
Trigger Actions and Order
AFTER Triggers
INSTEAD OF Triggers
Trigger Order
Security Implications with Triggers
Controlling, Filtering, and Enforcing Data Access
Enforcing Referential and Data Integrity
Troubleshooting Triggers
What Prevents Triggers from Executing
Viewing a Trigger
Apply Your Knowledge
Exercises
Review Questions
Exam Questions
Answers to Review Questions
Answers to Exam Questions
S TUDY S TRATEGIES
Be prepared to deal with questions that test your knowledge of the options that are used with the CREATE TRIGGER and ALTER TRIGGER statements. Make sure you know what options such as NOT FOR REPLICATION and WITH ENCRYPTION do, because these are the two main options that can be used with triggers. The WITH ENCRYPTION option should be easy to remember, because it prevents viewing the definition of the object, which is the same thing that it does for views and stored procedures.
Understand how INSTEAD OF triggers work. They are new and have a high chance of being on the exam. You should know how to implement these triggers on a view, because they are the only triggers that can be applied to a view. Views have specific requirements to allow updates to be carried out, and INSTEAD OF triggers can be used to allow updates on views that might not normally allow updates.
Be able to create professional triggers. Professional triggers should handle error trapping and single or multi-row operations. In addition to following the Step by Steps and exercises in this chapter, create triggers on your own. For information on error handling and trapping, read Chapter 9, "Stored Procedures and User -Defined Functions."
Finally, know what actions can cause a trigger to execute. There are only three: UPDATE , DELETE and INSERT . A single trigger can be tied to one or more of these actions. There can be multiple AFTER triggers for each action, but only a single INSTEAD OF trigger per action.
