Create a trigger that connects the class teacher and its department to the sub teacher and its department and if they dont match raise an error,

Question

Create a trigger before update that connects the class teacher and their department to the sub teacher and their department and if they dont have the same department raise an error

Teacher Table: tID, ClassteacherNo , subteacherNo (both FK of staffNo)
Staff Table: StaffNo, department

Using Oracle 18c

tried using a join but didnt know how to find the common department between the class teacher and sub

Answer 1

You need no INNER JOIN, you can use :new, to get the staff number and from that you can get the department

CREATE TABLe staff(StaffNo int, department int,
  CONSTRAINT staff_pk PRIMARY KEY (StaffNo))

INSERT INTO staff VALUES(1,1)

1 rows affected

INSERT INTO staff VALUES(2,2)

1 rows affected

INSERT INTO staff VALUES(3,1)

1 rows affected

INSERT INTO staff VALUES(4,1)

1 rows affected

CREATE TABLE teachers (tID int , ClassteacherNo int , subteacherNo int
  , CONSTRAINT fk_ClassTeacher
    FOREIGN KEY (ClassteacherNo)
    REFERENCES staff(StaffNo)
  , CONSTRAINT fk_SubTeacher
    FOREIGN KEY (subteacherNo)
    REFERENCES staff(StaffNo))

INSERT INTO teachers VALUES(1,1,3)

1 rows affected

CREATE OR REPLACE TRIGGER teachers_before_update
BEFORE UPDATE
   ON teachers
   FOR EACH ROW

DECLARE
   v_teacher_dep int;
   v_subteacher_dep int;
BEGIN

   -- Find department for teacher
   SELECT department INTO v_teacher_dep
   FROM staff 
  WHERE StaffNo = :new.ClassteacherNo;

   -- Find department for subteacher
   SELECT department INTO v_subteacher_dep
   FROM staff 
  WHERE StaffNo = :new.subteacherNo;


    IF v_subteacher_dep <> v_teacher_dep
      THEN
        RAISE_APPLICATION_ERROR(-20002,'The deaprtment differs between teacher and subteacher');
    END IF;
END;

/

SELECT * FROM USER_ERRORS;

UPDATE teachers SET subteacherNo = 2 WHERE tID = 1

ORA-20002: The deaprtment differs between teacher and subteacher
ORA-06512: at "FIDDLE_UUXCZJDQDWCKIGQJJFBH.TEACHERS_BEFORE_UPDATE", line 19
ORA-04088: error during execution of trigger 'FIDDLE_UUXCZJDQDWCKIGQJJFBH.TEACHERS_BEFORE_UPDATE'

UPDATE teachers SET subteacherNo = 4 WHERE tID = 1

1 rows affected

fiddle