Maintaining Data Integrity with Constraints and Triggers

Maintaining the integrity of the data in a SQL Server 2005 instance is crucially important to the reliable
operation of your business that uses SQL Server data. SQL Server uses several mechanisms, including
constraints and triggers, to help ensure data integrity. In this chapter, I tell you about constraints and
triggers that are tools to help maintain data integrity in SQL Server 2005.

A constraint is a rule that is enforced by SQL Server 2005. Microsoft suggests that, in SQL Server 2005,

constraints are the preferred way to enforce business rules.

A trigger is a special kind of stored procedure that executes in response to an event inside SQL Server.
A common use of triggers is to create an audit trail. For example, suppose you want to keep an audit
trail of who makes changes to prices in your online store. Each time someone modifies a row in the
relevant table, a trigger executes, which could store information such as the timeof the change, who
made the change, what the original price was, and what the new price is. Such information allows your

business to monitor trends in prices and also to find out who made any possibly wrong changes in price.

Constraints, Defaults, Rules, and Triggers

In this section, I describe constraints, defaults, rules, and triggers, which provide a range of ways to
enforce business rules inside SQL Server databases. In this article, I create a simple database by using

the following code.

Examples later in this aritcle use the ConstriggerDB database.

CREATE DATABASE ConstriggerDB

Constraints

Constraints (rules enforced by SQL Server 2005) provide a key way to ensure several aspects of data
integrity. Microsoft recommends that you use constraints rather than triggers in SQL Server 2005 to
ensure data integrity. The constraints supported in SQL Server 2005 are

� Primary key: Provides a way to uniquely identify each row in a table. A primary key constraint is a
    specialized form of a unique constraint.

� Unique constraint: Specifies that each value in a column is unique. One difference between a
    unique constraint and a primary key constraint is that a column with a unique constraint can contain
    NULL values, which are not permitted in a column that has a primary key constraint. If a column is a
    primary key or part of a primary key, you cannot also set up a unique constraint for that column.

� Check constraint: Specifies rules that values in a column must obey. A check constraint uses an
    expression to define the permitted values in a column. Later in this chapter, I show you how to define
    check constraints on a column.

Defaults

A default is a database object that you define and bind to a column. If during an insert operation, you
don't supply a value for a column to which the default is bound, then the default is inserted into that
column.

The following example creates a default of Unknown for values inserted into a table that records student
grades.

First, specify that you use the ConstriggerDB database:

USE ConstriggerDB

Then create the default called StudentGradeUnknown, specifying that it is the string Unknown:

CREATE DEFAULT StudentGradeUnknown AS 'Unknown'

Then create a simple table to store student grades:

CREATE TABLE StudentGrades

(StudentID int PRIMARY KEY,

Examination varchar(10),

Grade varchar(7))

At this stage, the StudentGradeUnknown default exists in the ConstriggerDB database. You need to bind
it to the Grade column in the StudentGrades table. Use this code, which makes use of the sp_bindefault
system stored procedure:

sp_bindefault 'StudentGradeUnknown', 'StudentGrades.Grade'

GO

To confirm that the default operates, use the following INSERT statement. Notice that no value is supplied
for the Grade column:

INSERT INTO StudentGrades(StudentID, Examination)

VALUES(1, 'XML101')

Also, insert a row where you supply a value in the Grade column:

INSERT INTO StudentGrades

VALUES(2, 'SVG101', 'A')

You can confirm the values in the StudentGrades table by using the following

code:

SELECT * FROM StudentGrades

In the first row shown in output, the value Unknown in the Grade column was supplied by the default bound
to that column. In the second row, you supplied a value for the Grade column so the default was not used.

Rules

Rules are included in SQL Server 2005 for backwards compatibility with SQL Server 2000. Check constraints
in SQL Server 2005 provide similar functionality.

Microsoft recommends that you use check constraints rather than rules in new code.

The following example that demonstrates how to create and use a rule uses a test table called TestTable in
the ConstriggerDB database, which I created with this code:

USE ConstriggerDB

CREATE TABLE TestTable

(ID int PRIMARY KEY,

Data char(1))

To create a rule in the ConstriggerDB database, use the following code:

CREATE RULE aThroughcOnly

AS

@ruleval >= 'a' AND @ruleval <= 'c'

The preceding rule specifies that the value must be lowercase, between lowercase a and lowercase c. You
also need to bind the rule you have created to a column. The following code binds it to the Data column in
the TestTable table:

sp_bindrule aThroughcOnly, 'TestTable.[Data]'

 

To insert a row with an allowed value, use the following code:

INSERT INTO dbo.TestTable

VALUES (1, 'b')

You should be prevented from entering a value that doesn't correspond to the rule you created. The following
attempts to insert a disallowed character in the Data column.

INSERT INTO dbo.TestTable

VALUES (2, 'd')

After you create a rule, you're likely to leave it in place unless you want to convert it to a constraint as
described in the preceding section. To unbind a rule from a specified column, use the following code:

sp_unbindrule 'dbo.TestTable.Data'

Triggers

Triggers are used to help maintain data integrity and enforce business rules. (Remember that a trigger is a
special kind of stored procedure that executes in response to an event inside SQL Server.) They complement
the protection of data integrity that constraints, defaults, and rules can provide. A trigger is a specialized
stored procedure. Unlike regular stored procedures, you cannot use an input parameter with a trigger, nor

can a trigger return a value. A trigger is associated with a particular table. When a specified event occurs,
the trigger executes.

Triggers are broadly divided into two groups:

� DDL triggers: Data Definition Language triggers

� DML triggers: Data Modification Language triggers

Triggers are classified as follows:

� INSTEAD OF triggers: These execute instead of the statement to which

    they are related.

� AFTER triggers: These execute after the statement to which they are

    related.

 

I describe and demonstrate several of these types of triggers later in another article soon.