Database Reference
In-Depth Information
Figure 7.32
Enter class listed in CLASS table.
TASK 6: VALIDATE BUSINESS CONSTR AINTS
Triggers are used to ensure that business constraints are met. Assume that the university
requires a student to get approval from his or her advisor if the student decides to withdraw
all his/her classes taken in one semester. In the next example, a trigger is used to prevent the
database from deleting the last class before getting the approval.
1. To create the trigger Drop_Class, enter the following SQL code in the New Query
editor:
CREATE TRIGGER Drop_Class
ON STUDENT_CLASS AFTER DELETE
AS
DECLARE @ClassCount As INT
BEGIN
SELECT @ClassCount
=
Count(*)
FROM STUDENT_CLASS S, DELETED D
WHERE D.StudentID
=
S.StudentID
IF (@ClassCount
=
0)
BEGIN
PRINT 'Need approval to drop the last class.'
ROLLBACK
END
END
2. Highlight the code and click
Run
. As shown in Figure 7.33, the trigger Drop_Class is
successfully created.
3. To verify that the trigger prevents a student from dropping all the classes before getting
approval, enter the following SQL statement to delete all classes for the student with the
id 12:
DELETE STUDENT_CLASS
WHERE StudentID
=
12
