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'
This trigger fires before the UPDATE statement on the SECTION table. First, you check if there is a new value for an instructor ID with the help of an IF-THEN statement. If the IF-THEN statement evaluates to TRUE, the instructor's ID is checked against the INSTRUCTOR table. If a new instructor ID does not exist in the INSTRUCTOR table, the exception is raised, and the trigger is terminated. Otherwise, all columns with NOT NULL constraints are populated with their respected values.
Consider the following UPDATE statement:
UPDATE section SET instructor_id = 220 WHERE section_id = 79;
The value 220 in this UPDATE statement does not exist in the INSTRUCTOR table and therefore is invalid. As a result, this UPDATE statement when run causes an error:
UPDATE section * ERROR at line 1: ORA-20000: This is not a valid instructor ORA-06512: at "STUDENT.SECTION_BU", line 11 ORA-04088: error during execution of trigger 'STUDENT.SECTION_BU'