CHECK constraints and rules are integrity constraints that go beyond those implied by a column's datatype. When a user enters a value, SQL Server checks that value against any CHECK constraint or rule created for the specified column to ensure that only values that adhere to the definition of the constraint or rule are accepted.
Although CHECK constraints and rules are essentially equivalent in functionality, CHECK constraints are easier to use and provide more flexibility. CHECK constraints are the preferred mechanism for restricting values in a column; SQL Server provides rules primarily as backward compatibility feature. A CHECK constraint can be conveniently defined when a column is defined, it can be defined on multiple columns, and it has access to all of SQL Server's built-in functions. A rule, however, must be defined and then bound separately to a single column or user-defined datatype, and it has access only to built-in functions that don't reference database objects.
Both CHECK constraints and rules can require that a value fall within a particular range, match a particular pattern, or match one of the entries in a specified list. An advantage of CHECK constraints is that they can depend on either the value of another column or columns in the row or on the value returned by one of the built-in functions. A rule can't reference other fields. As an example of applying a CHECK constraint or rule, a database containing information on senior citizens could have the CHECK constraint or the rule "age column must contain a value between 65 and 120 years." A birth certificate database could require that the date in the birth_date column be some date prior to the
current date.
Defaults
Defaults allow you to specify a value that SQL Server inserts if no value is explicitly entered in a particular field. For example, you can set the current date as the default value for an order_date field in a customer order record. Then, if a user or front-end application doesn't make an entry in the order_date field, SQL Server automatically inserts the current date. You can also use the keyword DEFAULT as a placeholder in an INSERT or UPDATE statement, which instructs SQL Server to set the value to the declared default value.
Triggers
Triggers are a special type of stored procedure. Stored procedures can be executed only when explicitly called; triggers are automatically invoked, or triggered, by SQL Server, and this is their main advantage. Triggers are associated with particular pieces of data and are called automatically whenever an attempt to modify that data is made, no matter what causes the modification (user entry or an application action).
Conceptually, a trigger is similar to a CHECK constraint or rule. SQL Server automatically activates triggers, constraints, and rules when an attempt is made to modify the data they protect. CHECK constraints and rules then perform fairly simple types of checks on the data—for example, "make sure the age field has a value between 0 and 120." Triggers, on the other hand, can enforce extremely elaborate restrictions on the data, which helps to ensure that the rules by which your business operates can't be subverted. Because triggers are a form of stored procedure, they have the full power of the Transact-SQL language at their disposal and they can invoke other stored and extended stored procedures. You can write a trigger that enforces complex business rules, such as the following:
Don't accept an order:
If the customer has any past due accounts with us
OR
If the customer has a bad credit rating by ACME Credit Service (with the trigger calling an extended procedure that automatically dials up ACME to get the credit rating)
OR
If the order is for more than $50,000 and the customer has had an account with us for less than six months
This integrity check is quite powerful. Yet the trigger to enforce it is simple to write. Triggers can also enforce
referential integrity, ensuring that relationships between tables are maintained. For example, a trigger can prohibit a
customer record from being deleted if open orders exist for the customer, or it can prohibit any new order for a customer for which no record exists. While you can do all this by declaring primary and foreign key constraints, triggers give you some added flexibility and functionality. Triggers allow you to supply your own error messages, which might be more useful than the default messages provided by SQL Server. Triggers also allow you to implement other forms of referential actions beside those available with the foreign key definition. I'll talk more about referential actions in Chapter 6, where I'll talk about constraints, and in Chapter 12, where I'll go into greater detail about triggers.
Triggers automatically execute whenever a specified change to a data object is attempted. A trigger executes once per statement, even if multiple rows would be affected. It has access to the before and after images of the data. SQL Server 2000 provides two kinds of triggers: "after" triggers and "instead-of" triggers. Instead-of triggers are what some people might call "before" triggers, but that name is not really accurate for SQL Server. An instead-of trigger defines actions to be carried out instead of the requested data modification. A before trigger would define actions to
perform prior to the data modification. By default, if a table or view has an instead-of trigger defined for a particular modification operation (INSERT, UPDATE, or DELETE), that data modification does not take place. Instead, the trigger action is executed.
An after trigger executes after the data has been modified. The trigger takes further action such as rolling back the current transaction or carrying out special referential actions.
3 4