CREATE OR REPLACE TRIGGER schedulation_meeting
  AFTER INSERT ON PEOPLE_AVAILABILITY -- BEFORE DELETE OR INSERT OR UPDATE ON
  FOR EACH ROW
--WHEN (new.Empno > 0)
DECLARE
    sal_diff NUMBER;
    numb_partecipant NUMBER;
    avaiability_people NUMBER;
    invited_people NUMBER;
    date_selected NUMBER;
    room_number NUMBER;
BEGIN
    -- retriving the value of the number of invited
    SELECT NumberOfInvitedPeople INTO invited_people FROM MEETING M
    WHERE M.Mcode = :NEW.MCode;


    -- retriving the value of the number of partecipant
    SELECT NumberOfParticipants INTO numb_partecipant FROM MEETING M
    WHERE M.Mcode = :NEW.MCode;

    IF (numb_partecipant == NULL) THEN
        numb_partecipant = 0;
    END IF;

    numb_partecipant=numb_partecipant+1

    -- update the number of partecipant
    UPDATE MEETING
    SET NumberOfParticipants = numb_partecipant
    WHERE M.Mcode = :NEW.MCode;


    IF(numb_partecipant==invited_people) THEN

        --select :NEW.NumberOfAvailablePeople into avaiability_people;
        SELECT DATE INTO date_selected FROM (SELECT COUNT(*)/numb_partecipant AS frac,DATE
        FROM PEOPLE_AVAILABILITY P)
        WHERE frac >= 0.8; -- maximum only one can be selected

        IF(date_selected!=NULL) THEN

            SELECT RoomCode INTO room_number
            FROM ROOM WHERE Capacity>= (SELECT COUNT(*)
            FROM PEOPLE_AVAILABILITY P
            WHERE DATE=date_selected)
            AND RoomCode NOT IN (
                SELECT RoomCode
                FROM MEETING
                WHERE MeetingDate=date_selected)

            IF (date_selected != NULL) THEN
                :NEW.RoomCode = room_number;
            END IF

        END IF

        --if (date_selected == NULL) then
        --    RAISE_APPLICATION_ERROR(-20001,'not the minimum amount of people can become in this date diocane');
        --end if
    END IF

END;