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 is not a valid section'); END IF; :NEW.ENROLL_DATE := SYSDATE; :NEW.CREATED_BY := USER; :NEW.CREATED_DATE := SYSDATE; :NEW.MODIFIED_BY := USER; :NEW.MODIFIED_DATE := SYSDATE; END;
Consider this trigger. It fires before the INSERT statement on the ENROLLMENT table. First, you validate new values for student ID and section ID. If one of the IDs is invalid, the exception is raised and the trigger is terminated. As a result, the INSERT statement causes an error. If both student and section IDs are found in the STUDENT and SECTION tables, respectively, the ENROLL_DATE, CREATED_ DATE, and MODIFIED_DATE are populated with current date, and columns CREATED_BY and MODIFIED_BY are populated with current user name.
Consider the following INSERT statement:
INSERT INTO enrollment (student_id, section_id) VALUES (777, 123);
The value 777, in this INSERT statement does not exist in the STUDENT table and therefore is invalid. As a result, this INSERT statement causes the following error:
INSERT INTO enrollment (student_id, section_id) * ERROR at line 1: ORA-20000: This is not a valid student ORA-06512: at "STUDENT.ENROLLMENT_BI", line 10 ORA-04088: error during execution of trigger 'STUDENT.ENROLLMENT_BI'