Triggers, Tipos de Funciones

Material Clase: clase10.pdfpdf

Ejercicio 01

Trigger para Auditar Eliminaciones de Productos

  1. Crea el trigger trg_AuditProductDelete para eliminar un producto de la tabla Products y registrar la información en una tabla de auditoría llamada ProductAudit
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;
  1. 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;
  1. 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'
);
BUSCARCtrl K