daily pastebin goal
7%
SHARE
TWEET

Untitled

a guest Oct 18th, 2018 65 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
  1. -- author: Fabian Carlos
  2.  
  3. -- INSERT para testar as triggers
  4.  
  5. -- INSERT INTO "passageiros" VALUES(333, 'Fabian Carlos');
  6. -- insert into "voo" VALUES(1);
  7. -- insert into "aeronaves" VALUES(1,'Boing 747');
  8. -- insert into "cidades" VALUES(1, 'Cuiabá', 'Brasil');
  9. -- insert into "aeroportos" VALUES(1, 'Aeroporto VG', 1);
  10. -- insert into "assentos" VALUES(1, 'AA+');
  11. -- insert into "assento_aeronaves" VALUES('AAA+', 1);
  12.  
  13. -- -----------------------------------------------------------------------------
  14. -- -----------------------------------------------------------------------------
  15.  
  16. -- Tabelas de logs "reservas" && "assentos"
  17. CREATE TABLE "logs_reservas"(
  18. "nome" VARCHAR(100),
  19. "horario" TIME,
  20. "acao" VARCHAR(40)
  21. );
  22.  
  23. CREATE TABLE "logs_assentos"(
  24. "ticket" integer,
  25. "nome" VARCHAR(100),
  26. "horario" TIME,
  27. "acao" VARCHAR(40)
  28. );
  29.  
  30. -- drop table "logs_reservas"
  31. -- drop table "logs_assentos"
  32.  
  33. -- 1 - REGISTRAR RESERVA DE PASSAGENS => REGISTRAR NOME DO USUÁRIO E O HORÁRIO
  34.  
  35. -- 1.1 function
  36. CREATE FUNCTION fn_log_reservas() RETURNS TRIGGER AS $$
  37. BEGIN
  38.   INSERT INTO "logs_reservas" VALUES (
  39.     (SELECT nome FROM "passageiros" WHERE cpf =  NEW.cpf_pass),
  40.     CURRENT_TIME,
  41.     'reservar'
  42.   );
  43.   RETURN NEW;
  44. END;
  45. $$ LANGUAGE plpgsql;
  46.  
  47. -- 1.2 trigger
  48. CREATE TRIGGER tr_log_reservas AFTER INSERT ON reservas FOR EACH ROW
  49.   EXECUTE PROCEDURE fn_log_reservas();
  50.  
  51. -- 1.3 insert test
  52. INSERT INTO "reservas" VALUES (2, '2012-03-04', 333);
  53.  
  54. -- 1.4 Select log de reservas
  55. SELECT * FROM "logs_reservas";
  56.  
  57. -- 1.5 remove triggers and functions
  58. -- drop trigger tr_log_reservas on reservas;
  59. -- drop function fn_log_reservas();
  60.  
  61. -- -----------------------------------------------------------------------------
  62. -- -----------------------------------------------------------------------------
  63.  
  64. -- 2 - MARCAR ASSENTO => REGISTRAR NOME DE USUÁRIO E O TICKET DA RESERVA
  65.  
  66. -- 2.1 function
  67. CREATE FUNCTION fn_logs_marcar_assento() RETURNS TRIGGER AS $$
  68. BEGIN
  69.   INSERT INTO "logs_assentos" VALUES (
  70.     NEW.cod,
  71.     (SELECT "passageiros"."nome" FROM "reserva_trechos", "passageiros", "reservas"
  72.         WHERE NEW.cod =  "reserva_trechos"."cod_assento"       
  73.         AND "passageiros"."cpf" =  "reservas"."cpf_pass" ),
  74.     CURRENT_TIME,
  75.     'reservar assento'
  76.   );
  77.   RETURN NEW;
  78. END;
  79. $$ LANGUAGE plpgsql;
  80.  
  81. -- 2.2 trigger
  82. CREATE TRIGGER tr_logs_marcar_assento AFTER INSERT ON assentos FOR EACH ROW
  83.   EXECUTE PROCEDURE fn_logs_marcar_assento();
  84.  
  85. -- 2.3 insert test
  86. INSERT INTO "assentos" VALUES (2, 'AAA+');
  87.  
  88. -- 2.4 Select log de reservas
  89. SELECT * FROM "logs_assentos";
  90.  
  91. -- 2.5 remove triggers and functions
  92. -- drop trigger tr_logs_marcar_assento on reservas;
  93. -- drop function fn_logs_marcar_assento();
  94.  
  95. -- -----------------------------------------------------------------------------
  96. -- -----------------------------------------------------------------------------
  97.  
  98. -- 3 - REGISTRAR RESERVA DE PASSAGENS => REGISTRAR NOME DO USUÁRIO E O HORÁRIO
  99.  
  100. -- 3.1 function
  101. CREATE FUNCTION fn_log_reservas_canceladas() RETURNS TRIGGER AS $$
  102. BEGIN
  103.   INSERT INTO "logs_reservas" VALUES (
  104.     (SELECT nome FROM "passageiros" WHERE cpf =  OLD.cpf_pass),
  105.     CURRENT_TIME,
  106.     'cancelado'
  107.   );
  108.   RETURN NEW;
  109. END;
  110. $$ LANGUAGE plpgsql;
  111.  
  112. -- 3.2 trigger
  113. CREATE TRIGGER tr_log_reservas_canceladas BEFORE DELETE ON reservas FOR EACH ROW
  114.   EXECUTE PROCEDURE fn_log_reservas_canceladas();
  115.  
  116. -- 3.3 insert test
  117. DELETE FROM "reservas" WHERE "cod" = 2;
  118.  
  119. -- 3.4 Select log de reservas
  120. SELECT * FROM "logs_reservas";
  121.  
  122. -- 3.5 remove triggers and functions
  123. -- drop trigger tr_log_reservas_canceladas on reservas;
  124. -- drop function fn_log_reservas_canceladas();
  125.  
  126. -- -----------------------------------------------------------------------------
  127. -- -----------------------------------------------------------------------------
  128.  
  129. -- 4 - REGISTRAR RESERVA DE PASSAGENS => REGISTRAR NOME DO USUÁRIO E O HORÁRIO
  130.  
  131. -- 4.1 function
  132. CREATE FUNCTION fn_logs_marcar_assento() RETURNS TRIGGER AS $$
  133. BEGIN
  134.   INSERT INTO "logs_assentos" VALUES (
  135.     NEW.cod,
  136.     (SELECT nome FROM "passageiros", "reservas"
  137.         WHERE NEW.cod_reserva =  "reservas"."cod",     
  138.         AND "passageiros"."cpf" =  "cod_reserva"."cpf_pass" ),
  139.     CURRENT_TIME,
  140.     'cancelar assento'
  141.   );
  142.   RETURN NEW;
  143. END;
  144. $$ LANGUAGE plpgsql;
  145.  
  146. -- 4.3 trigger
  147. CREATE TRIGGER tr_logs_marcar_assento BEFORE DELETE ON reserva_trechos FOR EACH ROW
  148.   EXECUTE PROCEDURE fn_logs_marcar_assento();
  149.  
  150. -- 4.4 Select log de reservas
  151. SELECT * FROM "logs_assentos";
  152.  
  153. -- 4.5 remove triggers and functions
  154. -- drop trigger tr_logs_marcar_assento on reservas;
  155. -- drop function fn_logs_marcar_assento();
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top