ex3_triggers_15042021

CREATE OR REPLACE FUNCTION validar_aluguel_cliente()
RETURNS TRIGGER AS
$BODY$
  DECLARE
     pag_aberto INT := 0;
     midias_disponiveis INT := 0;
     devolucao_aberto INT := 0;
  BEGIN
    SELECT count(*) INTO pag_aberto
    FROM payment
    WHERE customer_id = NEW.customer_id
     AND payment_date IS NULL;

    select count(*) INTO midias_disponiveis
    from rental
    where inventory_id = NEW.inventory_id
     AND return_date is null;

    select count(*) INTO devolucao_aberto
    from rental
    where customer_id = NEW.customer_id
     AND return_date is null;


    IF pag_aberto > 0 OR midias_disponiveis < 1 OR devolucao_aberto > 0 THEN
       RAISE EXCEPTION 'Cliente possui pendencias. Não é possível realizar o aluguel';
    END IF;

    NEW.rental_date := NOW();

    RETURN NEW;

  END
$BODY$ LANGUAGE plpgsql;


CREATE TRIGGER trg_valida_aluguel
BEFORE INSERT ON RENTAL
FOR EACH ROW
EXECUTE PROCEDURE validar_aluguel_cliente();