CREATE OR REPLACE PACKAGE BODY test_tw04n133 AS -- creating the a test procedure which is used for easy testing of the set teacher -- status trigger PROCEDURE testSst_tw04n133 (oldID NUMBER, newID NUMBER) IS currentStatus NUMBER; CURSOR getUnterrichtID IS SELECT u.unterricht_id FROM unterricht_tw04n133 u WHERE u.personen_id = oldID; result getStatus%ROWTYPE; BEGIN FOR result IN getStatus LOOP BEGIN SELECT p.status INTO currentStatus FROM personal_tw04n133 p WHERE p.personen_id = oldID; DBMS_OUTPUT.PUT_LINE('status of personen with personen_id = ' || oldID || ' is: ' || currentStatus); DBMS_OUTPUT.PUT_LINE('changing all entries with personen_id = ' || oldID || ' to personen_id = ' || newID); UPDATE unterricht_tw04n133 SET personen_id = newID WHERE unterricht_id = result.unterricht_id; SELECT p.status INTO currentStatus FROM personal_tw04n133 p WHERE p.personen_id = oldID; DBMS_OUTPUT.PUT_LINE('Status of person ' || oldID || ' changed to ' || currentStatus); END; END LOOP; END testSst_tw04n133; -- creating the a test procedure which is used for easy testing of the -- remove person trigger PROCEDURE testRemovePerson_tw04n133 (personenID NUMBER) IS BEGIN print_tw04n133 (personenID); DBMS_OUTPUT.PUT_LINE('deleting person with personen_id = ' || personenID || ' in table personen!'); print_tw04n133 (personenID); END testRemovePerson_tw04n133; -- print if a personen is in tables PROCEDURE print_tw04n133 (personenID NUMBER) IS isIn NUMBER; countEntries NUMBER; BEGIN countEntries := 0; DELETE FROM personen_tw04n133 WHERE personen_id = personenID; SELECT count(*) INTO isIn FROM personen_tw04n133 p WHERE p.personen_id = personenID; countEntries := countEntries + isIn; IF isIn > 0 THEN DBMS_OUTPUT.PUT_LINE('person with personen_id = ' || personenID || ' is in table personen!'); END IF; SELECT count(*) INTO isIn FROM personal_tw04n133 p WHERE p.personen_id = personenID; countEntries := countEntries + isIn; IF isIn > 0 THEN DBMS_OUTPUT.PUT_LINE('person with personen_id = ' || personenID || ' is in table personal!'); END IF; SELECT count(*) INTO isIn FROM studenten_tw04n133 s WHERE s.personen_id = personenID; countEntries := countEntries + isIn; IF isIn > 0 THEN DBMS_OUTPUT.PUT_LINE('person with personen_id = ' || personenID || ' is in table studenten!'); END IF; SELECT count(*) INTO isIn FROM noten_tw04n133 n WHERE n.personen_id = personenID; countEntries := countEntries + isIn; IF isIn > 0 THEN DBMS_OUTPUT.PUT_LINE('person with personen_id = ' || personenID || ' is in table noten!'); END IF; SELECT count(*) INTO isIn FROM studiengangsleiter_tw04n133 s WHERE s.personen_id = personenID; countEntries := countEntries + isIn; IF isIn > 0 THEN DBMS_OUTPUT.PUT_LINE('person with personen_id = ' || personenID || ' is in table studiengangsleiter!'); END IF; SELECT count(*) INTO isIn FROM unterricht_tw04n133 u WHERE u.personen_id = personenID; countEntries := countEntries + isIn; IF isIn > 0 THEN DBMS_OUTPUT.PUT_LINE('person with personen_id = ' || personenID || ' is in table unterricht!'); END IF; SELECT count(*) INTO isIn FROM fachbereich_tw04n133 f WHERE f.personen_id = personenID; countEntries := countEntries + isIn; IF isIn > 0 THEN DBMS_OUTPUT.PUT_LINE('person with personen_id = ' || personenID || ' is in table fachbereich!'); END IF; DBMS_OUTPUT.PUT_LINE('person with personen_id = ' || personenID || ' has ' || countEntries || ' entries!'); END print_tw04n133; -- creating the a test procedure which is used for easy testing the trigger for -- removing a doubled entry in the table unterricht PROCEDURE testRemoveDouble_tw04n133 (unterrichtID NUMBER, fachID NUMBER, personenID NUMBER) IS BEGIN -- assuming that the unterricht_id not exists and the fachID and personenID exists INSERT INTO noten_tw04n133 (unterricht_id, fach_id, personen_id) VALUES (unterrichtID, fachID, personenID); END testRemoveDouble_tw04n133; -- creating the a test procedure which is used for easy testing of the -- AutoIncrement on table noten PROCEDURE testAutoIncrement_tw04n133 (unterrichtID NUMBER, personenID NUMBER) IS BEGIN -- assuming that the id's used for the subject and person are valid INSERT INTO noten_tw04n133 (unterricht_id, personen_id) VALUES (unterrichtID, personenID); END testAutoIncrement_tw04n133;END test_tw04n133;