Database Reference
In-Depth Information
Figure 7.29
Updated message from trigger.
6. Highlight the code and click
Run
. As shown in Figure 7.28, the trigger Registration_
Info is successfully altered.
7. Insert another student with the following SQL statement. As shown in Figure 7.29, the
message has the added space between the words
at
and
Jul
.
To illustrate the use of the logic tables INSERTED and DELETED, consider the follow-
ing tasks:
TASK 5: VERIFY DATABASE INTEGRITY
In this task, suppose that you want to insert or update the table STUDENT_CLASS. After
STUDENT_CLASS is updated, a trigger Class_Checking is created to check if a class
inserted to the table STUDENT_CLASS exists in the table CLASS.
1. To create the trigger Class_Checking, enter the following SQL code in the New Query
editor:
CREATE TRIGGER Class_Checking
ON STUDENT_CLASS INSTEAD OF INSERT, UPDATE
AS
SET NOCOUNT ON
IF ((SELECT COUNT(C.ClassID)
FROM INSERTED I, CLASS C
WHERE C.ClassID
=
I.ClassID)
=
0)
BEGIN
PRINT 'Note: The class does not exist. No change!'
ROLLBACK
END
ELSE
INSERT STUDENT_CLASS
SELECT * FROM INSERTED
2. Highlight the code and click
Run
. As shown in Figure 7.30, the trigger Class_
Checking is successfully created.
