fahadkalil

ex3_triggers_15042021

Apr 15th, 2021
807
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION validar_aluguel_cliente()
  2. RETURNS TRIGGER AS
  3. $BODY$
  4.   DECLARE      
  5.      pag_aberto INT := 0;
  6.      midias_disponiveis INT := 0;
  7.      devolucao_aberto INT := 0;
  8.   BEGIN
  9.     SELECT count(*) INTO pag_aberto
  10.     FROM payment
  11.     WHERE customer_id = NEW.customer_id
  12.      AND payment_date IS NULL;
  13.    
  14.     select count(*) INTO midias_disponiveis
  15.     from rental
  16.     where inventory_id = NEW.inventory_id
  17.      AND return_date is null;
  18.  
  19.     select count(*) INTO devolucao_aberto
  20.     from rental
  21.     where customer_id = NEW.customer_id
  22.      AND return_date is null;
  23.    
  24.  
  25.     IF pag_aberto > 0 OR midias_disponiveis < 1 OR devolucao_aberto > 0 THEN
  26.        RAISE EXCEPTION 'Cliente possui pendencias. Não é possível realizar o aluguel';
  27.     END IF;
  28.    
  29.     NEW.rental_date := NOW();
  30.  
  31.     RETURN NEW;
  32.    
  33.   END
  34. $BODY$ LANGUAGE plpgsql;
  35.  
  36.  
  37. CREATE TRIGGER trg_valida_aluguel
  38. BEFORE INSERT ON RENTAL
  39. FOR EACH ROW
  40. EXECUTE PROCEDURE validar_aluguel_cliente();
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×