By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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