O'Reilly logo

Oracle® PL/SQL® Interactive Workbook, Second Edition by Elena Silvestrova, Benjamin Rosenzweig

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required