Triggers, Tipos de Funciones
Material Clase: clase10.pdfpdf
Ejercicio 01
Trigger para Auditar Eliminaciones de Productos
- Crea el trigger
trg_AuditProductDeletepara eliminar un producto de la tablaProductsy registrar la información en una tabla de auditoría llamadaProductAudit
Solución
CREATE TABLE ProductAudit (
AuditID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
ProductID NUMBER,
ProductName NVARCHAR2(100),
Category NVARCHAR2(50),
Price NUMBER(10, 2),
DeletionDate DATE
);
CREATE OR REPLACE TRIGGER trg_AuditProductDelete
AFTER DELETE ON Products
FOR EACH ROW
BEGIN
INSERT INTO ProductAudit (ProductID, ProductName, Category, Price, DeletionDate)
VALUES (:OLD.ProductID, :OLD.ProductName, :OLD.Category, :OLD.Price, SYSDATE);
DBMS_OUTPUT.PUT_LINE('Producto eliminado: ' || :OLD.ProductName || ', Categoría: ' || :OLD.Category || ', Precio: ' || :OLD.Price);
END;
- Generar uno para INSERTAR
Solución
CREATE TABLE ProductInsertAudit (
AuditID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
ProductID NUMBER,
ProductName NVARCHAR2(100),
Category NVARCHAR2(50),
Price NUMBER(10, 2),
InsertionDate DATE
);
CREATE OR REPLACE TRIGGER trg_AuditProductInsert
AFTER INSERT ON Products
FOR EACH ROW
BEGIN
INSERT INTO ProductInsertAudit (ProductID, ProductName, Category, Price, InsertionDate)
VALUES (:NEW.ProductID, :NEW.ProductName, :NEW.Category, :NEW.Price, SYSDATE);
DBMS_OUTPUT.PUT_LINE('Producto insertado: ' || :NEW.ProductName || ', Categoría: ' || :NEW.Category || ', Precio: ' || :NEW.Price);
END;
- Generar uno para ACTUALIZAR
Solución
CREATE TABLE ProductUpdateAudit (
AuditID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
ProductID NUMBER,
OldProductName NVARCHAR2(100),
NewProductName NVARCHAR2(100),
OldCategory NVARCHAR2(50),
NewCategory NVARCHAR2(50),
OldPrice NUMBER(10, 2),
NewPrice NUMBER(10, 2),
UpdateDate DATE
);
CREATE OR REPLACE TRIGGER trg_AuditProductUpdate
AFTER UPDATE ON Products
FOR EACH ROW
BEGIN
INSERT INTO ProductUpdateAudit (ProductID, OldProductName, NewProductName, OldCategory, NewCategory, OldPrice, NewPrice, UpdateDate)
VALUES (:OLD.ProductID, :OLD.ProductName, :NEW.ProductName, :OLD.Category, :NEW.Category, :OLD.Price, :NEW.Price, SYSDATE);
DBMS_OUTPUT.PUT_LINE('Producto actualizado: ID ' || :NEW.ProductID || ', Nombre: ' || :OLD.ProductName || ' a ' || :NEW.ProductName || ', Categoría: ' || :OLD.Category || ' a ' || :NEW.Category || ', Precio: ' || :OLD.Price || ' a ' || :NEW.Price);
END;
Ejercicio 02
Funciones de Una Sola Fila: Utiliza las funciones de una sola UPPER, LOWER y INITCAP para formatear los nombres de los clientes en diferentes formatos.
Solución
SELECT
CustomerID,
UPPER(FirstName) AS FirstName_Upper,
LOWER(LastName) AS LastName_Lower,
INITCAP(FirstName || ' ' || LastName) AS
NombreCompleto_Capitalizado
FROM Customers;
Ejercicio 03
Funciones de Conversión y Expresiones Condicionales: Utiliza las funciones de conversión (TO_CHAR, TO_DATE) y una expresión condicional (CASE) para mostrar la fecha de registro de los clientes en diferentes formatos y categorizarlos en función de su antigüedad.
Solución
SELECT
CustomerID,
FirstName,
LastName,
TO_CHAR(RegistrationDate, 'DD-MON-YYYY') AS RegistrationDate_Formatted,
CASE
WHEN MONTHS_BETWEEN(SYSDATE, RegistrationDate) / 12 >= 1 THEN 'Antiguo'
ELSE 'Nuevo'
END AS CategoriaCliente
FROM Customers;
Ejercicio 04
Funciones de Grupo y Agrupamiento de Filas: Utiliza las funciones de grupo SUM, AVG, MAX con las cláusulas GROUP BY y HAVING para mostrar el total de ventas, el promedio, el número de órdenes y la orden más alta por cliente, filtrando solo aquellos con más de 2 órdenes.
Solución
SELECT
CustomerID,
SUM(TotalAmount) AS TotalVentas,
AVG(TotalAmount) AS PromedioVentas,
COUNT(OrderID) AS NumeroOrdenes,
MAX(TotalAmount) AS OrdenMaxima
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 2;
Ejercicio 05
Consultas sobre Múltiples Tablas usando Uniones y Autouniones: Realiza una consulta que use INNER JOIN para mostrar los detalles de cada orden, incluyendo el nombre del cliente, y un LEFT OUTER JOIN para mostrar todas las órdenes, incluidas aquellas sin detalles.
Solución
SELECT
o.OrderID,
c.FirstName || ' ' || c.LastName AS NombreCliente,
o.OrderDate,
o.TotalAmount
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;
SELECT
o.OrderID,
o.OrderDate,
o.TotalAmount,
od.ProductID,
od.Quantity,
od.LineTotal
FROM Orders o
LEFT OUTER JOIN OrderDetails od ON o.OrderID = od.OrderID;
Ejercicio 06
Subconsultas de Una Sola fila y de Varias Filas con Operadores ALL o ANY: Utiliza subconsultas de una sola fila y de varias filas con los operadores ALL y ANYpara encontrar los productos que tienen un precio superior al de todos los productos de la categoría Accessories y aquellas que tienen un precio inferior al de cualquier producto de la categoría Electronics.
Solución
SELECT ProductName, Price
FROM Products
WHERE Price > ALL (
SELECT Price
FROM Products
WHERE Category = 'Accessories'
);
SELECT ProductName, Price
FROM Products
WHERE Price < ANY (
SELECT Price
FROM Products
WHERE Category = 'Electronics'
);