-- trigger which is used to delete all entries of a person by personen_id
-- when it is deleted in the personen table

CREATE OR REPLACE TRIGGER removePerson_tw04n133
BEFORE DELETE ON personen_tw04n133
FOR EACH ROW
    DECLARE
        is_student number;
        is_staff number;
BEGIN
    -- look if person is a student
    SELECT COUNT(*) INTO is_student FROM studenten_tw04n133 s
    WHERE s.personen_id = :old.personen_id;
    --look if person is in personal table
    SELECT COUNT(*) INTO is_staff FROM studenten_tw04n133 s
    WHERE s.personen_id = :old.personen_id; 
    -- deleting all entries a student can have
    IF is_student > 0 THEN
        DELETE FROM noten_tw04n133 WHERE personen_id = :old.personen_id;
        DELETE FROM studenten_tw04n133 WHERE personen_id = :old.personen_id;
    END IF;
    -- deleting all entries a person of the staff can have
    IF is_staff > 0 THEN
        DELETE FROM studiengangsleiter_tw04n133 WHERE personen_id = :old.personen_id;
        DELETE FROM unterricht_tw04n133 WHERE personen_id = :old.personen_id;
        DELETE FROM fachbereich_tw04n133 WHERE personen_id = :old.personen_id;
        DELETE FROM personal_tw04n133 WHERE personen_id = :old.personen_id;
    END IF;
END;