Triggers play a role in the database environment in constraining data that is modified in the database. Some other techniques that are used for constraining data include views, stored procedures, defaults, rules, and constraints. The data constraining method that is best for you at any given time depends on a number of variables , such as permissions on tables, the number of tables tested against, and desired performance or CPU cost. EXAM TIP Trigger and Constraints Know the differences between these data constraining methods , their benefits, drawbacks, and when to implement each. 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. ViewsViews can be used to restrict what data is visible in a database. Because you can restrict what is visible in the view, you can restrict what data can be deleted. Views can also be created with the WITH CHECK OPTION . When the view is created using WITH CHECK OPTION , the view enables data to be added to the database only if the resulting data will be visible in the view. If you compare views to triggers, triggers offer much greater flexibility in controlling what can be added, deleted, or modified in the database. Although views are very restricted in their power, they also have a very low processor cost. Views can restrict inserts into the database, but their main function is to restrict visibility of data. For more information about views, consult Chapter 7, "Working with Views." Stored ProceduresStored procedures can be used to modify data rather than allowing users to access that data tables directly. When using stored procedures, you are able to write simple or complex logic that is performed when the stored procedure executes. Rather than the user issuing the statements manually, the stored procedure is used to make the modification based on parameters passed to the procedure. Stored procedures can impose rules and policies on the modification, and can perform complex procedures without the possibility of user -induced errors. When comparing stored procedures to triggers, and assuming that the code used in each 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 or not. This function of triggers is useful, because some people are granted access directly to the table and the code will still be executed. For more information about stored procedures, consult Chapter 9. DefaultsDefaults do not restrict what data is put into a table, but rather are used to provide values for columns into which you are not inserting data. An example of a default would be a company that is based in the United States with 80% of its client base in the U.S. This company might decide to place a default on the country field in its customer table, because in most cases it will be correct. This default is used only if the INSERT statement does not provide a country value. A trigger can be used to provide default values when values are missing on an insert, but at a higher cost than defaults. Defaults are a lower-level option, so they can be applied more efficiently ; however, triggers are able to make decisions on what values should be applied based on other criteria. Whenever possible, keep it simple by using defaults, and only resort to triggers if you need to make a choice between values. For more information about defaults, consult Chapter 3. RulesRules have been implemented in SQL Server 2000 for backward compatibility. They operate in a fashion similar to CHECK constraints ”which are now preferred ”with the exception that only one rule can be bound to a column of a table, whereas there can be multiple CHECK constraints on a single column. The following code shows how to implement a rule to restrict the guess column in the guesses table to a number between 1 and 10. CREATE RULE guess_check AS @gu BETWEEN 1 and 10 GO CREATE TABLE guesses ( user_id int PRIMARY KEY, guess int, ) GO sp_bindrule guess_check, 'guesses.guess' GO Rules offer very basic tests for valid data. Rules apply only to inserted and modified data, but perform no actions on deleted rows. Rules operate with a very low processor cost. For more information about rules, consult Chapter 3. ConstraintsThere are five basic constraints that can be used to restrict inserts and updates: NOT NULL , CHECK , UNIQUE , PRIMARY KEY , and FOREIGN KEY . These constraints place restrictions on what data can be inserted or updated in the database. FOREIGN KEY constraints can also be used to restrict what data can be deleted from the database. Constraints offer a fair amount of flexibility, but do not possess the level of code or logic that is present in a trigger. Triggers can reference columns in other tables, and can evaluate the change in the data because they can examine the data both before and after modification. In general, constraints should be used when possible because they are more efficient and cost less on the CPU, and triggers should be used whenever constraints are not powerful enough to perform the job. Constraints are executed after INSTEAD OF triggers, but before AFTER triggers. The following code tests for valid data by means of a constraint, which was discussed earlier: CREATE TABLE guesses ( user_id int PRIMARY KEY, guess int, CONSTRAINT check_guess CHECK (guess BETWEEN 1 and 10 ) ) The following would be an AFTER trigger method of implementing the same task: CREATE TABLE guesses ( user_id int PRIMARY KEY, guess int ) GO CREATE TRIGGER guess_trigger ON guesses FOR INSERT, UPDATE AS IF UPDATE (guess) IF NOT ((SELECT guess FROM inserted) BETWEEN 1 AND 10) BEGIN ROLLBACK TRAN RAISERROR ('Guesses must be between 1 and 10.', 16, 1) END In this example, the trigger is performing only a rudimentary task, but does enable you to create a custom error message. You will see how triggers are created later in this chapter. For a quick comparison of the difference between the different types of constraint methods, see Table 8.1. Table 8.1. Comparing Constraints, Triggers, and Stored Procedures
For more information about constraints, consult Chapter 3. The next section examines the creation and management of triggers, and later in the chapter you will learn the differences between different types of triggers. |