Implementación de Bases de Datos Activas y Triggers SQL: Ejercicios Resueltos
Clasificado en Informática
Escrito el en 
español con un tamaño de 12,87 KB
Base de Datos II
Resoluciones (Modelo) de Ejercicios Prácticos
Base de Datos Activas – Práctico 1
1) Creación de Tabla Empleados y Dominio Personalizado
A continuación, se presenta la definición de la tabla Empleados con sus respectivos atributos y restricciones:
CREATE TABLE Empleados
(
DNI CHAR(8) NOT NULL PRIMARY KEY,
Legajo CHAR(6) UNIQUE,
Ape_Nom VARCHAR2(50),
Cargo NUMBER CHECK(Cargo BETWEEN 50 AND 120),
Sueldo DEC(8,2) CHECK(Sueldo <= 30000)
);
Alternativa: Crear un tipo de datos de usuario a partir de un tipo de dato estándar; luego, lo podemos referenciar, por ejemplo, al definir una tabla.
CREATE DOMAIN Documento INT(8) CHECK(Documento > 0);
Se modificaría la definición de la tabla Empleados para el atributo DNI, que haría referencia al tipo de dato Documento creado previamente:
DNI Documento PRIMARY KEY,
Implementación de un Trigger para controlar la actualización del sueldo:
CREATE TRIGGER Control_Sueldo
BEFORE UPDATE OF Sueldo ON Empleados -- Cuando se actualiza el atributo Sueldo de la tabla Empleados
FOR EACH ROW -- Se activa este disparador, controlando que el Sueldo no se
BEGIN -- incremente en más del 20% del valor del Sueldo anterior
IF :new.Sueldo > (:old.Sueldo * 1.20) THEN
-- Sentencia que cancela la actualización (ej. RAISE_APPLICATION_ERROR('Error', 'Aumento de sueldo excede el 20%'));
END IF;
END;
Una forma de incorporar comentarios en las sentencias SQL es mediante el uso de -- o /* */:
-- Esto es un comentario de una línea
Y otra manera es:
/*
Comentario
en varias
líneas
*/
2) Definición de Tabla Seguros con Claves Foráneas y Restricciones
Definición de la tabla Seguros, incluyendo claves foráneas y restricciones de valores:
CREATE TABLE Seguros
(
Nro_Poliza CHAR(8) NOT NULL PRIMARY KEY,
DNI CHAR(6) REFERENCES Clientes(DNI),
Marca CHAR(7) CHECK(Marca IN ('Ford', 'Renault', 'Fiat', 'Peugeot', 'VW')),
Modelo CHAR(4) DEFAULT '2015',
Patente CHAR(10),
Nro_Motor VARCHAR2(30),
Imp_Pagar DEC(10,2) NOT NULL,
FOREIGN KEY(Marca, Modelo) REFERENCES Vehiculos(Marca, Modelo)
);
3) Gestión de Pedidos con Triggers de Control de Stock
Definición de la tabla Pedidos con sus atributos y un trigger para controlar el stock:
CREATE TABLE Pedidos
(
Nro_Orden CHAR(6) NOT NULL UNIQUE,
Fecha_Pedido DATE DEFAULT CURRENT_DATE,
CUIT CHAR(11) NOT NULL,
CUIT_Proveedor CHAR(11) NOT NULL, -- Se agrega este atributo
REFERENCES Clientes(CUIT),
PRIMARY KEY(CUIT, Nro_Orden),
Nro_Producto NUMBER,
Tipo_Pedido CHAR(1) CHECK(Tipo_Pedido IN ('M', 'C', 'G', 'H', 'N')),
Cant_Pedida INT CHECK(Cant_Pedida > 0)
);
Trigger para controlar la cantidad pedida en relación con el stock disponible:
CREATE TRIGGER Control_Pedido
BEFORE INSERT ON Pedidos
FOR EACH ROW
BEGIN
/* La condición evalúa que la nueva cantidad pedida no sea mayor al valor del stock actual
del producto que se rescata de la tabla Stock_Productos */
IF :new.Cant_Pedida > (SELECT Stock_Actual FROM Stock_Productos
WHERE Producto = :new.Nro_Producto) THEN
-- Sentencia que cancela la inserción (ej. RAISE_APPLICATION_ERROR('Error', 'Cantidad pedida superior al stock disponible'));
ELSE
UPDATE Stock_Productos
SET Stock_Actual = Stock_Actual - :new.Cant_Pedida
WHERE Producto = :new.Nro_Producto;
END IF;
END;
4) Adición de Restricciones a la Tabla Pedidos
Estas restricciones se agregarían a las sentencias del ejercicio 3), al definir la tabla Pedidos:
CONSTRAINT ClaveProducto FOREIGN KEY (Nro_Producto, Tipo_Pedido) REFERENCES Productos(Id_Producto, Id_Tipo),
CONSTRAINT CantidadPermitida CHECK(Cant_Pedida BETWEEN 10 AND 2000);
Para añadir una restricción a una tabla existente:
ALTER TABLE Pedidos WITH NO CHECK ADD
CONSTRAINT ControlCuit CHECK (CUIT <> CUIT_Proveedor);
5) Creación de Tablas Clientes y Facturas con Trigger de Cálculo
Definición de un Dominio para el código de cliente:
CREATE DOMAIN CodigoCliente INT(7) CHECK (CodigoCliente > 0);
Definición de la tabla Clientes:
CREATE TABLE Clientes
(
Cod_Cliente CodigoCliente PRIMARY KEY,
Nombre VARCHAR2(40) NOT NULL,
Direccion VARCHAR2(35) NOT NULL,
Cod_Postal CHAR(5),
FOREIGN KEY (Cod_Postal) REFERENCES CodPostales(CP),
Importe_Base DEC(8,2)
);
Definición de la tabla Facturas:
CREATE TABLE Facturas
(
Nro_Factura CHAR(8) NOT NULL PRIMARY KEY,
Fecha_Fact DATE,
Cliente_Factura CodigoCliente,
FOREIGN KEY(Cliente_Factura) REFERENCES Clientes(Cod_Cliente),
Tipo_Descuento INT(2) CHECK(Tipo_Descuento BETWEEN 5 AND 20),
IVA INT(2) CHECK((IVA = 15) OR (IVA = 21)),
Importe DEC(10,2) NOT NULL
);
Trigger para calcular el importe de la factura:
CREATE TRIGGER Calcular_Importe
BEFORE UPDATE OF Importe_Base ON Clientes
FOR EACH ROW
DECLARE
Resultado DEC(10,2); -- Variable auxiliar declarada dentro del trigger
BEGIN
-- Descontamos del importe base el respectivo importe de acuerdo al valor de descuento
Resultado := :new.Importe_Base - ((:new.Tipo_Descuento * :new.Importe_Base) / 100);
/* Al importe obtenido en el paso anterior, que almacenamos en el campo auxiliar ‘Resultado’,
debemos incrementar dicho valor de acuerdo al porcentaje del IVA a aplicar */
Resultado := Resultado + ((:new.IVA * Resultado) / 100);
UPDATE Facturas
SET Importe = Resultado
WHERE Cliente_Factura = :new.Cod_Cliente;
END;
6) Registro y Eliminación de Empleados Dados de Baja
Definición de la tabla Empleado_Baja para registrar empleados eliminados:
CREATE TABLE Empleado_Baja
(
DNI CHAR(8),
Legajo CHAR(6),
Cargo NUMBER,
Usuario VARCHAR2(15),
Fecha DATE
);
Trigger para registrar empleados eliminados:
CREATE TRIGGER Empleado_Eliminado -- Se registran en una tabla aquellos empleados dados de
AFTER DELETE ON Empleados -- baja
FOR EACH ROW
BEGIN
INSERT INTO Empleados_Baja VALUES (:old.DNI, :old.Legajo, :old.Cargo, USER, SYSDATE);
END;
Trigger para eliminar el usuario asociado a un empleado dado de baja:
CREATE TRIGGER Baja_Usuario -- Eliminamos el usuario del empleado, con el cual accedía al sistema
AFTER DELETE ON Empleados -- de gestión de la empresa a la cual pertenecía
FOR EACH ROW
BEGIN
DELETE FROM Usuarios WHERE Usuarios.DNI = :old.DNI;
END;
7) Modificación y Desactivación de Triggers
Para reemplazar un trigger existente (por ejemplo, cambiando la condición de eliminación):
REPLACE TRIGGER Baja_Usuario
AFTER DELETE ON Empleados
FOR EACH ROW
BEGIN
DELETE FROM Usuarios WHERE Usuarios.Legajo = :old.Legajo;
END;
Para deshabilitar un trigger específico:
ALTER TRIGGER Calcular_Importe DISABLE;
Para deshabilitar todos los triggers de una tabla:
ALTER TABLE Consumos DISABLE ALL TRIGGERS;
8) Adición de Columnas y Trigger de Recargo en Facturas
Dependerá del manejador de base de datos que usemos, ya que permite distintos formatos para agregar columnas a una tabla.
Opción 1: Añadir columnas individualmente
ALTER TABLE Facturas ADD Fecha_Vto DATE;
ALTER TABLE Facturas ADD Fecha_Pago DATE;
ALTER TABLE Facturas ADD Intereses INT NULL;
Opción 2: Añadir múltiples columnas en una sola sentencia
ALTER TABLE Facturas ADD Fecha_Vto DATE, Fecha_Pago DATE, Intereses INT NULL;
Trigger para aplicar un recargo en facturas si la fecha de pago excede la fecha de vencimiento:
CREATE TRIGGER Recargo_Factura
AFTER UPDATE OF Fecha_Pago ON Facturas
FOR EACH ROW
BEGIN
IF :new.Fecha_Pago > :new.Fecha_Vto THEN
UPDATE Facturas
SET Intereses = 1
WHERE Cliente_Factura = :new.Cod_Cliente;
END IF;
END;
Comentarios Generales sobre Triggers
En los triggers, podemos hacer referencia a los valores que insertamos o actualizamos anteponiendo al nombre de cada atributo :new.; y para hacer referencia a los valores de una fila que vamos a eliminar, anteponemos :old.. Ejemplos:
:new.Cantidad_Pedida- Cantidad pedida de un cierto producto:old.DNI- DNI del empleado eliminado de la respectiva tabla
Recuerden que estas resoluciones pueden sufrir cambios de acuerdo a otra forma de plantearlos que cada uno desee incorporar, pero siempre llegando a soluciones válidas para los ítems requeridos.