-- package storing the personen_id of the teacher which is to be modified
CREATE OR REPLACE PACKAGE storage_tw04n133 AS
    teacher number;
END storage_tw04n133;

-- trigger which is used to store the id of the teacher which is being modified
-- called BEFORE the modification is executed.
CREATE OR REPLACE TRIGGER setStatusB_tw04n133
BEFORE UPDATE OR DELETE OF personen_id ON unterricht_tw04n133
FOR EACH ROW
BEGIN
    storage_tw04n133.teacher := :old.personen_id;
END;

-- trigger which actually checks the amount of subjects still assigned to the teacher
-- and updates the status of it in the personal_tw04n133 if required.
CREATE OR REPLACE TRIGGER setStatusA_tw04n133
AFTER UPDATE OR DELETE OF personen_id ON unterricht_tw04n133
DECLARE
    subject_count number;
BEGIN
    SELECT COUNT(*) INTO subject_count FROM unterricht_tw04n133 u
    WHERE u.personen_id = storage_tw04n133.teacher;
    
    IF(subject_count <= 0) THEN
        UPDATE personal_tw04n133 SET status = -1 WHERE personen_id = storage_tw04n133.teacher;
    END IF;
END;