Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 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();
Add Comment
Please, Sign In to add comment