DROP TABLE IF EXISTS Vehiculo CASCADE;
CREATE TABLE Vehiculo
(
placa Varchar(6) NOT NULL,
color Varchar(20),
modelo Varchar(20),
marca Varchar(12),
estadoVehiculo Varchar(10) DEFAULT 'Libre',
CONSTRAINT pk_vehiculos PRIMARY KEY (placa)
);
DROP TABLE IF EXISTS Alquiler;
CREATE TABLE Alquiler
(
idAlquiler Integer NOT NULL,
fecha Date,
numHoras Integer DEFAULT 0,
valor Real DEFAULT 0,
placaVehiculo Varchar(6) NOT NULL,
estadoAlquiler Varchar(10) DEFAULT 'Iniciado',
CONSTRAINT pk_alquiler PRIMARY KEY (idAlquiler)
);
ALTER TABLE Alquiler
ADD FOREIGN KEY (placaVehiculo)
REFERENCES Vehiculo (placa)
ON UPDATE CASCADE
ON DELETE RESTRICT;
/*
* Este trigger valida que los dos estado posibles
* para vehiculos sea 'Libre' o 'Alquilado' ANTES
* de INSERTAR O ACTUALIZAR
*/
DROP FUNCTION IF EXISTS trigger_verificar_vehiculo_estado CASCADE;
CREATE OR REPLACE FUNCTION trigger_verificar_vehiculo_estado()
RETURNS TRIGGER
AS
$$
DECLARE
BEGIN
CASE TG_OP
/* ********************************* */
/* Permitimos insertar solo */
/* para estados de vehiculos validos */
/* ********************************* */
WHEN 'INSERT' THEN
/*Validamos los estados del Vehiculo*/
IF NEW.estadoVehiculo IN('Libre','Alquilado') THEN RETURN NEW;
END IF;
/* Informamos al usuario del problema */
RAISE NOTICE 'ERROR: Problemas al insertar el vehiculo %. Estado del Vehículo Inválido => %',
NEW.placa, NEW.estadoVehiculo;
/* Rechazamos el registro */
RETURN NULL;
/* *************************************************** */
/* Se ejecuta solo si hubo un cambio en estadoVehiculo */
/* *************************************************** */
WHEN 'UPDATE' THEN
/* Validamos los estados del Vehiculo */
IF NEW.estadoVehiculo IN('Libre','Alquilado') THEN /* Aceptamos el cambio */
RETURN NEW;
ELSE
/* Informamos al usuario del problema */
RAISE NOTICE 'ERROR: Problemas al actualizar el vehiculo %. Estado Vehículo Inválido => %',
NEW.placa, NEW.estadoVehiculo;
/*
* Se devuelve el registro original,
* pero mas efciente es un RETURN NULL
*/
RETURN OLD;
END IF;
END CASE;
/* NO DEBERIA LLEGAR AQUI */
RETURN NEW;
END
$$
LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trigger_vehiculo ON Vehiculo;
CREATE TRIGGER trigger_vehiculo
/* Se ejecutara UPDATE solo si el valor de estadoVehiculo cambia */
BEFORE INSERT OR UPDATE OF estadoVehiculo
ON Vehiculo
FOR EACH ROW
EXECUTE FUNCTION trigger_verificar_vehiculo_estado();
/*
* Este trigger valida que los dos estado posibles
* para alquiler sea 'Iniciado' o 'Terminado' ANTES
* de INSERTAR O ACTUALIZAR
*/
DROP FUNCTION IF EXISTS trigger_verificar_alquiler_estado CASCADE;
CREATE OR REPLACE FUNCTION trigger_verificar_alquiler_estado()
RETURNS TRIGGER
AS
$$
DECLARE
mi_placa varchar(6);
BEGIN
/* ***************************************** */
/* Si no existe la placa en maestra vehiculo */
/* rechazamos cualquier operacion */
/* ***************************************** */
SELECT placa INTO mi_placa
FROM Vehiculo
WHERE placa = NEW.placaVehiculo;
IF NOT FOUND THEN
RAISE NOTICE 'ERROR: La placa % no esta registrada en la maestra de Vehículos, No se insertara el registro. Verifíque por favor.',
NEW.placaVehiculo;
RETURN NULL;
END IF;
CASE TG_OP
/* ********************************************************* */
/* Permitimos insertar solo para estados de alquiler validos */
/* ********************************************************* */
WHEN 'INSERT' THEN
/* Validamos los estados de alquiler */
IF NEW.estadoAlquiler IN('Iniciado','Terminado') THEN CASE NEW.estadoAlquiler
WHEN 'Iniciado' THEN
UPDATE Vehiculo
SET estadoVehiculo='Alquilado'
WHERE placa = NEW.placaVehiculo;
WHEN 'Terminado' THEN
UPDATE Vehiculo
SET estadoVehiculo='Libre'
WHERE placa = NEW.placaVehiculo;
END CASE;
RETURN NEW;
END IF;
/* Informamos al usuario del problema */
RAISE NOTICE 'ERROR: Problemas al insertar el estado de alquiler del vehiculo %. Estado de Alquiler Inválido => %',
NEW.placaVehiculo, NEW.estadoAlquiler;
/* Rechazamos el registro */
RETURN NULL;
/* *************************************************** */
/* Se ejecuta solo si hubo un cambio en estadoAlquiler */
/* *************************************************** */
WHEN 'UPDATE' THEN
/* Validamos los estados del Vehiculo */
IF NEW.estadoAlquiler IN('Iniciado','Terminado') THEN CASE NEW.estadoAlquiler
WHEN 'Iniciado' THEN
UPDATE Vehiculo
SET estadoVehiculo='Alquilado'
WHERE placa = NEW.placavehiculo;
WHEN 'Terminado' THEN
UPDATE Vehiculo
SET estadoVehiculo='Libre'
WHERE placa = NEW.placavehiculo;
END CASE;
/* Aceptamos el cambio */
RETURN NEW;
END IF;
/* Informamos al usuario del problema */
RAISE NOTICE 'ERROR: Problemas al actualizar el estado de alquiler del vehiculo %. Estado de Alquiler Inválido => %',
NEW.placaVehiculo, NEW.estadoAlquiler;
/* Se devuelve el registro original, pero mas efciente es un RETURN NULL */
RETURN OLD;
END CASE;
/* NO DEBERIA LLEGAR AQUI */
RETURN NEW;
END
$$
LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trigger_alquiler ON alquiler;
CREATE TRIGGER trigger_aqluiler
/* Se ejecutara UPDATE solo si el valor de estado alquiler cambia */
BEFORE INSERT OR UPDATE OF estadoAlquiler
ON Alquiler
FOR EACH ROW
EXECUTE FUNCTION trigger_verificar_alquiler_estado();
DO
$$
DECLARE
BEGIN
RAISE NOTICE ' ';
RAISE NOTICE '/* *********************************** */';
RAISE NOTICE '/* >>>>>>>> TESTING VEHICULOS <<<<<<<< */';
RAISE NOTICE '/* *********************************** */';
RAISE NOTICE ' ';
RAISE NOTICE '/* Insertando Vehículos... */';
INSERT INTO Vehiculo(placa, color, modelo, marca)
VALUES
('AAA1','Amarillo','2020','Fiat'), ('AAA2','AZul','2019','Fiat'), ('ABA3','Rojo','2018','Honda'), ('ABA4','Blanco','2017','Honda'), ('ACA5','Negro','2020','Ford'), ('ACA6','Verde','2019','Ford'), ('ADA7','Cyan','2018','Chevrolet'), ('ADA8','Gris','2017','Chevrolet'), ('AEA9','Naranja','2020','Nissan');
RAISE NOTICE ' ';
RAISE NOTICE '/* Inserciones de Vehículos Rechazadas... */';
/* Estos registro seran rechazados por el trigger */
INSERT INTO Vehiculo(placa, color, modelo, marca, estadoVehiculo)
VALUES
('ABC10','Violeta','2020','Opel','Mtto'), ('ABC11','Marron','2020','Saab','Operativo');
/* Se actualiza correctamente por que no ha cambiado el estadoVehiculo */
RAISE NOTICE ' ';
RAISE NOTICE '/* Actualizando Vehículos... */';
UPDATE Vehiculo SET modelo='2016' WHERE placa='AAA1';
UPDATE Vehiculo SET modelo='2017' WHERE placa='AAA2';
/* Estamos cambiando el valor estadoVehiculo pero seran rechazados */
RAISE NOTICE ' ';
RAISE NOTICE '/* Actualizaciones de Vehículos Rechazadas... */';
UPDATE Vehiculo SET modelo='2015', estadoVehiculo='Operativo' WHERE placa='ABA3';
UPDATE Vehiculo SET modelo='2014', estadoVehiculo='Mtto' WHERE placa='ABA4';
RAISE NOTICE ' ';
RAISE NOTICE '/* ************************************ */';
RAISE NOTICE '/* >>>>>>>> TESTING ALQUILERES <<<<<<<< */';
RAISE NOTICE '/* ************************************ */';
RAISE NOTICE ' ';
RAISE NOTICE '/* Insertando Alquileres... */';
INSERT INTO Alquiler(idAlquiler, fecha, numhoras, valor, placaVehiculo, estadoAlquiler)
VALUES
(1, NOW()::date, 10, 100, 'AAA1', 'Iniciado'),
(2, NOW()::date, 10, 100, 'AAA2', 'Iniciado'),
(3, NOW()::date, 10, 100, 'ABA3', 'Iniciado'),
(4, NOW()::date, 10, 100, 'ABA4', 'Iniciado'),
(5, NOW()::date, 10, 100, 'ACA5', 'Terminado');
RAISE NOTICE ' ';
RAISE NOTICE '/* Inserciones rechazadas de Alquileres... */';
/* Estos dos registro no se insertan por estados de alquiler inválidos */
INSERT INTO Alquiler(idAlquiler, fecha, numhoras, valor, placaVehiculo, estadoAlquiler)
VALUES
(6, NOW()::date, 10, 100, 'ACA6', 'Intermedio'),
(7, NOW()::date, 10, 100, 'ADA7', 'Intermedio');
RAISE NOTICE ' ';
RAISE NOTICE '/* Inserción rechazada: el vehículo no existe... */';
/* Este registro nunca se insertara, placa no existe en la maestra de Vehículos */
INSERT INTO Alquiler(idAlquiler, fecha, numhoras, valor, placaVehiculo, estadoAlquiler)
VALUES
(8, NOW()::date, 10, 100, 'AGA8', 'Terminado');
/* ACTUALIZAMOS ALQUILERES */
RAISE NOTICE ' ';
RAISE NOTICE '/* Actualizacion de Alquileres... */';
UPDATE Alquiler SET estadoAlquiler = 'Terminado' WHERE idAlquiler = 1;
UPDATE Alquiler SET estadoAlquiler = 'Terminado' WHERE idAlquiler = 2;
/* Se rechazan estas Actualizaciones */
RAISE NOTICE ' ';
RAISE NOTICE '/* Actualizacion rechazadas de Alquileres */';
UPDATE Alquiler SET estadoAlquiler = 'Culminado' WHERE idAlquiler = 3;
UPDATE Alquiler SET estadoAlquiler = 'Terminado' WHERE idAlquiler = 4;
/* Se debe aceptar esta actualizacion */
RAISE NOTICE ' ';
RAISE NOTICE '/* Actualizacion valida de Alquileres... */';
UPDATE Alquiler SET estadoAlquiler = 'Iniciado' WHERE idAlquiler = 5;
/* No actualiza no existe la PK */
RAISE NOTICE ' ';
RAISE NOTICE '/* Actualizacion rechazada por PK inexistente... */';
UPDATE Alquiler SET estadoAlquiler = 'Iniciado' WHERE idAlquiler = 20;
END
$$
LANGUAGE plpgsql;
