CREATE OR REPLACE TRIGGER maintain_guest_bookings AFTER
    UPDATE OF booking_state ON booking
    FOR EACH ROW
    --WHEN (old.booking_from > sysdate)
DECLARE
    v_guest_bookings   NUMBER(5);
BEGIN
    IF
        :new.booking_from > SYSDATE AND :new.booking_state = 'C'
    THEN
        raise_application_error(-20003,'Future booking cannot have booking state as C (completed).');
    ELSE
        SELECT
            guest_bookings
        INTO
            v_guest_bookings
        FROM
            guest
        WHERE
            guest_no =:new.guest_no;

        IF
            :new.booking_state = 'C' AND :old.booking_state <> 'C'
        THEN
            UPDATE guest
                SET
                    guest_bookings = v_guest_bookings + 1
            WHERE
                guest_no =:new.guest_no;

        ELSIF :old.booking_state = 'C' AND :new.booking_state <> 'C' THEN
            UPDATE guest
                SET
                    guest_bookings = v_guest_bookings - 1
            WHERE
                guest_no =:new.guest_no;

        END IF;

    END IF;
END;
/