Oracle® PL/SQL® Interactive Workbook, Second Edition

Chapter 17 Triggers

1)

Create the following trigger: Create or modify a trigger on the ENROLLMENT table that fires before an INSERT statement. Make sure all columns that have NOT NULL and foreign key constraints defined on them are populated with their proper values.

A1:

Answer: Your trigger should look similar to the following: CREATE OR REPLACE TRIGGER enrollment_bi BEFORE INSERT ON ENROLLMENT FOR EACH ROW DECLARE v_valid NUMBER := 0; BEGIN SELECT COUNT(*) INTO v_valid FROM student WHERE student_id = :NEW.STUDENT_ID; IF v_valid = 0 THEN RAISE_APPLICATION_ERROR (-20000, 'This is not a valid student'); END IF; SELECT COUNT(*) INTO v_valid FROM section WHERE section_id = :NEW.SECTION_ID; IF v_valid = 0 THEN RAISE_APPLICATION_ERROR (-20001, 'This ...