Database Reference
In-Depth Information
Figure 7.19
Test trigger implementing dynamic logic.
In the SQL statement in Figure 7.19, the grade in the class with the id 1006 taken by the
student with the id 11 was changed from NULL to B. After the change, the student's cumulative
GPA is 2.5.
7.4.2.3 Keeping Database Integrity
Another important application of triggers is to ensure that referential integrity is kept after data
have been inserted, deleted, or updated. As an example, let us consider the following situation.
By the university regulation, if the enrollment of a class is small, the class should be canceled. In
this case, you need to remove the class information from the table CLASS and other related tables
in the database Class_Registration. When deleting a class from the table CLASS, an INSTEAD
OF trigger will be used to remove the rows referencing the deleted class from other related tables
CLASS_CLASSROOM, STUDENT_CLASS, and FACULTY_CLASS. he SQL statement
below creates the trigger called KeepIntegrity to accomplish this task:
CREATE TRIGGER KeepIntegrity
ON CLASS
INSTEAD OF DELETE AS
BEGIN
DELETE STUDENT_CLASS
WHERE ClassID IN (SELECT ClassID FROM DELETED)
DELETE FACULTY_CLASS
WHERE ClassID IN (SELECT ClassID FROM DELETED)
DELETE CLASS_CLASSROOM
WHERE ClassID IN (SELECT ClassID FROM DELETED)
DELETE CLASS
WHERE ClassID IN (SELECT ClassID FROM DELETED)
END
Instead of deleting the class in the table CLASS, which may cause an error on violating the
referential constraints, the above trigger irst deletes those rows in the tables STUDENT_CLASS,
FACULTY_CLASS, and CLASS_CLASSROOM, which are referencing the ClassID values
stored in the table DELETED and then deletes the corresponding row in the table CLASS.
To test the trigger, let us delete the classes that have no student enrolled in them. he DELETE
statement in Figure 7.20 can accomplish this task.
In the subquery, a RIGHT JOIN operator is used to make sure that the classes with no
student are included so that the HAVING clause can identify these classes. Without RIGHT
Search WWH ::




Custom Search