Constraining Data Modifications


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.


Views

Views 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 Procedures

Stored 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.

Defaults

Defaults 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.

Rules

Rules 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.

Constraints

There 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

Constraining Method

Description

Benefit

Drawback

NOT NULL

Forces a column to not accept NULL values.

Offers the most efficient way to prevent NULL columns.

It only verifies that the column is not NULL , it cannot test for specific values.

CHECK

Performs a Boolean test on simple conditions, such as entered value was BETWEEN 1 AND 10 .

This offers a quick test to ensure that the value entered is valid. This constraint does not use a high level of processor activity.

The types of test are very rudimentary. When test fails the transaction is rolled back, there is no other error handling that can perform additional actions.

UNIQUE

Ensures that all values entered in the column or set of columns are unique from each other. NULL values are allowed.

UNIQUE constraints are more efficient than unique indexes.

Like CHECK constraints, when the constraint fails, the only action that is performed is a rollback of the transaction.

PRIMARY KEY

Ensures that all values entered in the column or set of columns are unique. NULL values are not allowed.

Every table should have a PRIMARY KEY , since a unique column will ensure a unique record, and may improve the speed of sorting and searching.

Like CHECK constraints, when the constraint fails, the only action that is performed is a rollback of the transaction.

FOREIGN KEY

References values that are stored in another table. This constraint can also force entered values to exist in the referenced table.

FOREIGN KEY constraints can support an ON DELETE action, as well as ON UPDATE action. Either of these actions can fail the transaction with NO ACTION or CASCADE , which will fail the command and rollback the transaction, or update or delete the referenced information.

FOREIGN KEY constraints can only rollback transactions, and cannot perform more complex actions when data fails the constraint.

TRIGGER

Automatically execute SQL scripts when data update events occur. Events include INSERT , UPDATE , and DELETE statements. INSTEAD OF triggers fire before constraints are checked, while AFTER triggers are fired after the constraints are checked.

Triggers are capable of performing a complex sequence of commands that can carry out tests and actions on the data. These tests and actions can be more in-depth than CHECK , PRIMARY KEY , and FOREIGN KEY constraints.

Triggers are more processor intensive than constraint actions, and should only be used when constraints will not satisfy the requirements of the data.

STORED PROCEDURE

An SQL script that can carry out an action.

Stored procedures can be executed instead of INSERT , UPDATE , and DELETE commands. They can then perform a complex sequence of tests and actions prior to issuing the INSERT , UPDATE , and DELETE commands.

Stored procedures do not execute automatically with data modifications, and must be specifically called.

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.



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net