When constraints involve multiple tables or are complex, you should use a trigger instead of a constraint. Additionally, you can nest triggers—that is, one trigger fires another—if you have enabled that feature on your SQL Server installation. Nested triggers allow you to implement a sequence of updates, inserts, and deletes throughout the database tables as one trigger initiates another; however, you need to carefully assess potential consequences of executing nested triggers. The design team is not using nested triggers in the Eval database; however, SQL Server 7.0 also allows the use of multiple triggers for each INSERT, UPDATE, or DELETE statement and there are instances in which this feature can be put to use in the Eval database. It is often easier to maintain a trigger when the trigger performs only one task, and having multiple triggers for each table action allows you to perform separate tasks for the same table action using different triggers. Keep in mind, however, that when a table has multiple triggers for the same action, the triggers fire in an unpredictable order, so the outcome you want cannot rely on the triggers executing in a specific sequence.
The database designers identify triggers to maintain counts (Size in the Group table), compare counts (Size compared to MaxSize in the Group table), and calculate aggregate values (Average and StandardDeviation in the Activity table). The following table lists the tables, the table actions, and the purpose of each trigger.
| Table | Actions | Purpose of trigger |
|---|---|---|
| Activity | Insert, Update | When an Activity record has a foreign key relationship with a Group record, the DueDate of the Activity record must be greater than or equal to the BeginDate of the Group record. When the Group record has an EndDate, the DueDate must be less than or equal to the EndDate. |
| Group | Insert, Update | When the LocationId in the Group record is not NULL, the MaxSize of the Group record must be less than or equal to the MaxOccupancy of the Location record. |
| Group | Insert, Update, Delete | When the Period column is not NULL, each Period column value for an evaluator must be unique. |
| Location | Update | When a Location record has a foreign key relationship with a Group record, the number in the MaxOccupancy column of Location must be greater than or equal to the MaxSize of the Group. |
| PersonActivity | Insert | When an Activity record has a foreign key relationship with a Location record, the count of PersonGroup records for an activity must be less than or equal to the MaxOccupancy of the Location. |
| PersonActivity | Insert, Update | The Grade in the PersonActivity record must belong to the GradeScale specified by the Activity, ActivityType, or Group record. |
| PersonActivity | Insert, Update, Delete | The AdjScore field in the PersonActivity table updates the Average and StandardDeviation columns in the Activity table. |
| PersonGroup | Insert | The number of Persons with a PersonType equal to I associated with a Group must be less than or equal to MaxSize. |
| PersonGroup | Insert, Delete | Updates Size in Group. |
The triggers on the Eval database tables will be written when the coding of the PT application begins. Table scripts (tablename.sql) in Code Listings and Object Reference contain the script for all triggers.