Question : Trigger

PL/SQL ERD Schools Schema

1. The trigger should fire when a record is inserted into the classrooms table or when a record's teacher_id or subject_id is updated.

2. If the insert or update would result in a new classroom that has a teacher teaching a subject that they don't teach, an exception should occur with a message similar to:

   Sarah Garcia does not teach Math

3. To test your trigger, run the following two inserts. The first should succeed and

   the second should fail with the message shown above:

   INSERT INTO classrooms (teacher_id, subject_id, semester, year) VALUES (1, 1, 'spring', 2022);

   INSERT INTO classrooms (teacher_id, subject_id, semester, year) VALUES (2, 1, 'spring', 2022);

I would like to get the response to this problem.

My trigger

***I got this error.

PLS-00049: bad bind variable 'NEW.PERSON_ID' Errors: check compiler log**

CREATE OR REPLACE TRIGGER subject_trigger BEFORE INSERT OR UPDATE OF teacher_id,subject_id ON CLASSROOMS FOR EACH ROW DECLARE l_full_name VARCHAR2(50); l_subject subjects.subject%type; l_subject_id teachers.subject_id%type; l_teacher_id teachers.teacher_id%type; invalid_subject EXCEPTION; BEGIN Select first_name || ' ' || last_name as full_name ,subject,subject_id,teacher_id INTO l_full_name,l_subject,l_subject_id,l_teacher_id FROM PEOPLE WHERE person_id = :NEW.person_id AND subject_id = :NEW.subject_id; IF :NEW.subject_id l_subject_id THEN RAISE invalid_subject; END IF; EXCEPTION WHEN invalid_subject THEN RAISE_APPLICATION_ERROR(-20101, l_full_name || ' does not teach' || l_subject || ' . '); END subject_trigger;

SYSTEM.SCHOOLS P SCHOOL_ID NUMBER * SCHOOL_NAME VARCHAR2 (35 BYTE) ADDRESS VARCHAR2 (60 BYTE) CITY VARCHAR2 (15 BYTE) REGION VARCHAR2(15 BYTE) POSTAL_CODE VARCHAR2(10 BYTE) COUNTRY VARCHAR2 (15 BYTE) PRINCIPAL_ID NUMBER SCHOOLS_PK (SCHOOL_ID) SCHOOL_PRINCIPAL_FK (PRINCIPAL_ID) SYSTEM.PEOPLE P. PERSON_ID NUMBER IF SCHOOL_ID NUMBER * FIRST_NAME VARCHAR2(10 BYTE) .LAST_NAME VARCHAR2 (20 BYTE) BIRTH DATE DATE * ADDRESS VARCHAR2(00 BYTE) CITY VARCHAR2 (15 BYTE) REGION VARCHAR2 (15 BYTE) POSTAL_CODE VARCHAR2 (10 BYTE) COUNTRY VARCHAR2 (15 BYTE) > PEOPLE_PK (PERSON_ID) 3 SYS_C0021064 (SCHOOL_ID) SYSTEM TEACHERS P TEACHER_ID NUMBER PERSON_ID NUMBER SUBJECT_ID NUMBER SALARY NUMBER TEACHERS_PK (TEACHER_ID) SYS_C0021067 (PERSON_ID) SYS_C0021088 (SUBJECT_ID) F 11 SYSTEM.SUBJECTS JP SUBJECT ID NUMBER SUBJECT VARCHAR2 (60 BYTE) SUBJECTS_PK (SUBJECT_ID) SYSTEM.PRINCIPALS P PRINCIPAL_ID NUMBER PERSON_ID NUMBER SALARY NUMBER PRINCIPALS_PK (PRINCIPAL_ID) SYS_C0021074 (PERSON_ID) SYSTEM.STUDENTS P STUDENT_ID NUMBER PERSON_ID NUMBER GRADE_LEVEL NUMBER STUDENTS_PK (STUDENT_ID) SYS_C0021071 (PERSON_ID) SYSTEM.CLASSROOM_STUDENTS CLASSROOM_STUDENT_ID NUMBER CLASSROOM ID NUMBER STUDENT_ID NUMBER GRADE NUMBER CLASSROOM_STUDENTS_PK (CLASSROOM_STUDENT_ID) 3 SYS_C0021082 (STUDENT_ID) SYS_C0021081 (CLASSROOM_ID) SYSTEM.CLASSROOMS P CLASSROOM_ID NUMBER TEACHER_ID NUMBER SUBJECT_ID NUMBER SEMESTER VARCHAR2(0 BYTE) YEAR NUMBER (4) - CLASSROOMS_PK (CLASSROOM_ID) 3 SYS_C0021077 (TEACHER_ID) SYS_C0021078 (SUBJECT_ID)