DBS UE6A1

-- 1.) Schreiben Sie einen Datenbanktrigger, der - im Falle des L?schens eines
-- Passagiers - garantiert, dass auch alle Gep?ckst?cke des Passagiers entfernt
-- werden.

CREATE OR REPLACE TRIGGER before_deleteFromPassglist_row
before DELETE ON passagierliste
FOR each ROW
DECLARE
v_gepaeckStuecke NUMBER;
BEGIN
  DBMS_OUTPUT.put_line('Person to be deleted '||:old.personID);

  SELECT COUNT(gepaeckID)
  INTO v_gepaeckStuecke
  FROM gepaeck
  WHERE personID = :old.personid
  AND flugid = :old.flugid;

  IF v_gepaeckStuecke > 0 THEN
  DELETE FROM gepaeck
  WHERE personID = :old.personid
  AND flugid = :old.flugid;
  DBMS_OUTPUT.put_line('Deleted '||v_gepaeckStuecke||' pieces of Gepaeck from PID: '||:old.personID||' on FlugID: '||:old.flugID);
  ELSE
  DBMS_OUTPUT.put_line('PID: '||:old.personID||' checked in without any Gepaeck');
  END IF;
END;
/

SELECT * FROM passagierliste p
join gepaeck g ON p.personid = g.personid AND p.flugid = g.flugid;

SELECT * FROM gepaeck;

DELETE FROM passagierliste
WHERE personid = 6 AND flugid = 1;

BEGIN
  DELETE FROM passagierliste
  WHERE flugid = 1;
END;
/
--NICHT VEREGSSEN NACH DIESEM STATEMENT ZU ROLLBACKEN!!!