Triggers are used to maintain referential integrity, which assures that vital data in your database—such as the unique identifier for a given piece of data—remains accurate and can be used as the database changes. Referential integrity is coordinated through the use of primary and foreign keys.
The primary key is a column or combination of columns whose values uniquely identify a row. The value cannot be null and must have a unique index. A table with a primary key is eligible for joins with foreign keys in other tables. Think of the primary key table as the master table in a master-detail relationship. There can be many such master-detail groups in a database.
You can use sp_primarykey to mark a primary key. This marks the key for use with sp_helpjoins and adds it to the syskeys table.
For example, the title_id column is the primary key of titles. It uniquely identifies the books in titles and joins with title_id in titleauthor, salesdetail, and roysched. The titles table is the master table in relation to titleauthor, salesdetail, and roysched.
The “Diagram of the pubs2 database” shows how the pubs2 tables are related. The “Diagram of the pubs3 database” provides the same information for the pubs3 database.
The foreign key is a column, or combination of columns, whose values match the primary key. A foreign key does not have to be unique. It is often in a many-to-one relationship to a primary key. Foreign key values should be copies of the primary key values. That means no value in the foreign key should exist unless the same value exists in the primary key. A foreign key may be null; if any part of a composite foreign key is null, the entire foreign key must be null. Tables with foreign keys are often called detail tables or dependent tables to the master table.
You can use sp_foreignkey to mark foreign keys in your database. This flags them for use with sp_helpjoins and other procedures that reference the syskeys table. The title_id columns in titleauthor, salesdetail, and roysched are foreign keys; the tables are detail tables. In most cases, you can enforce referential integrity between tables using the referential constraints described under “Specifying referential integrity constraints”, because the maximum number of references allowed for a single table is 200. If a table exceeds that limit, or has special referential integrity needs, use referential integrity triggers.
Referential integrity triggers keep the values of foreign keys in line with those in primary keys. When a data modification affects a key column, triggers compare the new column values to related keys by using temporary work tables called trigger test tables. When you write your triggers, base your comparisons on the data that is temporarily stored in the trigger test tables.
