Algumas triggers, procedures do banco Passagens Aereas

triggers_procedures_ifmt.sql

-- author: Fabian Carlos

-- INSERT para testar as triggers

-- INSERT INTO "passageiros" VALUES(333, 'Fabian Carlos');
-- insert into "voo" VALUES(1);
-- insert into "aeronaves" VALUES(1,'Boing 747');
-- insert into "cidades" VALUES(1, 'Cuiabá', 'Brasil');
-- insert into "aeroportos" VALUES(1, 'Aeroporto VG', 1);
-- insert into "assentos" VALUES(1, 'AA+');
-- insert into "assento_aeronaves" VALUES('AAA+', 1);

-- -----------------------------------------------------------------------------
-- -----------------------------------------------------------------------------

-- Tabelas de logs "reservas" && "assentos"
CREATE TABLE "logs_reservas"(
"nome" VARCHAR(100),
"horario" TIME,
"acao" VARCHAR(40)
);

CREATE TABLE "logs_assentos"(
"ticket" integer,
"nome" VARCHAR(100),
"horario" TIME,
"acao" VARCHAR(40)
);

-- drop table "logs_reservas"
-- drop table "logs_assentos"

-- 1 - REGISTRAR RESERVA DE PASSAGENS => REGISTRAR NOME DO USUÁRIO E O HORÁRIO

-- 1.1 function
CREATE FUNCTION fn_log_reservas() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO "logs_reservas" VALUES (
	(SELECT nome FROM "passageiros" WHERE cpf =  NEW.cpf_pass), 
	CURRENT_TIME,
	'reservar'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 1.2 trigger
CREATE TRIGGER tr_log_reservas AFTER INSERT ON reservas FOR EACH ROW 
  EXECUTE PROCEDURE fn_log_reservas();

-- 1.3 insert test
INSERT INTO "reservas" VALUES (2, '2012-03-04', 333);

-- 1.4 Select log de reservas
SELECT * FROM "logs_reservas";

-- 1.5 remove triggers and functions
-- drop trigger tr_log_reservas on reservas;
-- drop function fn_log_reservas();

-- -----------------------------------------------------------------------------
-- -----------------------------------------------------------------------------

-- 2 - MARCAR ASSENTO => REGISTRAR NOME DE USUÁRIO E O TICKET DA RESERVA

-- 2.1 function
CREATE FUNCTION fn_logs_marcar_assento() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO "logs_assentos" VALUES (
	NEW.cod,
	(SELECT "passageiros"."nome" FROM "reserva_trechos", "passageiros", "reservas" 
		WHERE NEW.cod =  "reserva_trechos"."cod_assento"		
		AND "passageiros"."cpf" =  "reservas"."cpf_pass" ),
	CURRENT_TIME,
	'reservar assento'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2.2 trigger
CREATE TRIGGER tr_logs_marcar_assento AFTER INSERT ON assentos FOR EACH ROW 
  EXECUTE PROCEDURE fn_logs_marcar_assento();

-- 2.3 insert test
INSERT INTO "assentos" VALUES (2, 'AAA+');

-- 2.4 Select log de reservas
SELECT * FROM "logs_assentos";

-- 2.5 remove triggers and functions
-- drop trigger tr_logs_marcar_assento on reservas;
-- drop function fn_logs_marcar_assento();

-- -----------------------------------------------------------------------------
-- -----------------------------------------------------------------------------

-- 3 - REGISTRAR RESERVA DE PASSAGENS => REGISTRAR NOME DO USUÁRIO E O HORÁRIO

-- 3.1 function
CREATE FUNCTION fn_log_reservas_canceladas() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO "logs_reservas" VALUES (
	(SELECT nome FROM "passageiros" WHERE cpf =  OLD.cpf_pass), 
	CURRENT_TIME,
	'cancelado'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 3.2 trigger
CREATE TRIGGER tr_log_reservas_canceladas BEFORE DELETE ON reservas FOR EACH ROW 
  EXECUTE PROCEDURE fn_log_reservas_canceladas();

-- 3.3 insert test
DELETE FROM "reservas" WHERE "cod" = 2;

-- 3.4 Select log de reservas
SELECT * FROM "logs_reservas";

-- 3.5 remove triggers and functions
-- drop trigger tr_log_reservas_canceladas on reservas;
-- drop function fn_log_reservas_canceladas();

-- -----------------------------------------------------------------------------
-- -----------------------------------------------------------------------------

-- 4 - REGISTRAR RESERVA DE PASSAGENS => REGISTRAR NOME DO USUÁRIO E O HORÁRIO

-- 4.1 function
CREATE FUNCTION fn_logs_marcar_assento() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO "logs_assentos" VALUES (
	NEW.cod,
	(SELECT nome FROM "passageiros", "reservas" 
		WHERE NEW.cod_reserva =  "reservas"."cod",		
		AND "passageiros"."cpf" =  "cod_reserva"."cpf_pass" ),
	CURRENT_TIME,
	'cancelar assento'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 4.3 trigger
CREATE TRIGGER tr_logs_marcar_assento BEFORE DELETE ON reserva_trechos FOR EACH ROW 
  EXECUTE PROCEDURE fn_logs_marcar_assento();

-- 4.4 Select log de reservas
SELECT * FROM "logs_assentos";

-- 4.5 remove triggers and functions
-- drop trigger tr_logs_marcar_assento on reservas;
-- drop function fn_logs_marcar_assento();