DAIS_TRIGGER

CREATE OR REPLACE
TRIGGER checkTrainCrew
AFTER INSERT OR UPDATE
ON TrainCrew
DECLARE

CURSOR cursS IS SELECT * FROM Staff;
CURSOR cursTC IS SELECT * FROM TrainCrew;
CURSOR cursTR IS SELECT * FROM TrainRide;

scr INT;
dcr INT;
gcr INT;

BEGIN

FOR cTR IN cursTR LOOP

  scr := 0;
  dcr := 0;
  gcr := 0;

  FOR cS IN cursS LOOP
    FOR cTC IN cursTC LOOP
      IF (cTR.idTrainRide = cTC.idTrainRide) THEN
        IF(cS.idStaff = cTC.idStaff) THEN

          IF(cs.position = 's') THEN
            scr := scr + 1;
          END IF;

          IF(cs.position = 'g') THEN
            gcr := gcr + 1;
          END IF;

          IF(cs.position = 'd') THEN
            dcr := dcr + 1;
          END IF;

          IF (scr > 1 OR dcr > 1 OR gcr > 1)  THEN

            DBMS_OUTPUT.PUT_LINE('ERROR');
            DELETE FROM TrainCrew WHERE idStaff = cTC.idStaff AND idTrainRide = cTC.idTrainRide;
            EXIT;

          END IF;
        END IF;
      END IF;
    END LOOP;
  END LOOP;
END LOOP;
END;