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.

***I got this error. Please if you can help me . I've been trying to figure out the error . I'm not expert in PL/SQL

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;