Ejercicios Prácticos de Triggers en Bases de Datos Oracle

A continuación, se presentan una serie de ejercicios prácticos para la implementación de triggers en bases de datos Oracle, junto con sus soluciones. Estos ejercicios cubren diversos escenarios y funcionalidades de los triggers, permitiendo comprender su utilidad y aplicación en la gestión de bases de datos.

Bloque Anónimo para Actualizar la Tabla Empleados

Ejercicio 5: Crea un bloque anónimo que modifique la tabla Empleados para añadirle un nuevo dato que sea el número de pedidos que gestiona y, a continuación, actualizarla con los valores que debe tener dicho dato para cada uno de los empleados.

ALTER TABLE empleados ADD NumeroPedidos NUMBER(4) DEFAULT 0;

DECLARE
  CURSOR c_clientespedidos IS
    SELECT codigoempleadorepventas
    FROM clientes NATURAL JOIN pedidos;
BEGIN
  FOR r_clientespedidos IN c_clientespedidos LOOP
    UPDATE empleados
    SET numeropedidos = numeropedidos + 1
    WHERE codigoempleado = r_clientespedidos.codigoempleadorepventas;
  END LOOP;
END;

Trigger para Actualizar el Número de Pedidos al Insertar un Nuevo Pedido

Ejercicio 6: Crea un trigger llamado MasPedido de tal forma que cuando se inserta un nuevo registro en la tabla Pedidos se actualice el dato correspondiente en la tabla Empleados.

CREATE OR REPLACE TRIGGER MasPedidos
BEFORE INSERT ON pedidos
FOR EACH ROW
BEGIN
  UPDATE empleados
  SET numeropedidos = numeropedidos + 1
  WHERE codigoempleado = (SELECT codigoempleadorepventas FROM clientes WHERE codigocliente = :new.codigocliente);
END;

Trigger para Modificar el Límite de Crédito al Modificar la Cantidad de Pagos

Ejercicio 7: Crea un trigger llamado CambiarLimite de tal forma que cuando se modifica la cantidad de la tabla Pagos para un determinado cliente, se modifique con el mismo porcentaje el límite de crédito que tiene dicho cliente en la tabla Clientes.

CREATE OR REPLACE TRIGGER CambiarLimite
AFTER UPDATE OF cantidad ON pagos
FOR EACH ROW
DECLARE
  porcentaje NUMBER(5,2);
BEGIN
  porcentaje := :new.cantidad / :old.cantidad;
  UPDATE clientes
  SET limitecredito = limitecredito * porcentaje
  WHERE codigocliente = :new.codigocliente;
END;

Modificación del Trigger para Considerar Solo Cambios al Alza

Ejercicio 8: Modifica el trigger anterior para que solo se efectúen modificaciones en el límite de crédito en el caso de que los cambios en cantidad sean al alza y no a la baja.

CREATE OR REPLACE TRIGGER CambiarLimite
AFTER UPDATE OF cantidad ON pagos
FOR EACH ROW
WHEN (new.cantidad > old.cantidad)
DECLARE
  porcentaje NUMBER(5,2);
BEGIN
  porcentaje := :new.cantidad / :old.cantidad;
  UPDATE clientes
  SET limitecredito = limitecredito * porcentaje
  WHERE codigocliente = :new.codigocliente;
END;

Trigger para Avisar sobre la Revisión del Código Postal al Cambiar la Ciudad

Ejercicio 9: Crea un trigger llamado CambioCiudad tal que después de modificar la ciudad de un cliente sin haber modificado el código postal de un Cliente, visualice un mensaje que avise que hay que revisar los valores de código postal.

CREATE OR REPLACE TRIGGER CambioCiudad
AFTER UPDATE ON clientes
FOR EACH ROW
WHEN (old.codigopostal = new.codigopostal AND old.ciudad <> new.ciudad)
BEGIN
  dbms_output.put_line('Hay que revisar el codigo postal)');
END;

Triggers para Impedir Modificaciones en Horas Específicas

Ejercicio 1: Se desea tener un disparador llamado MalasHorasPedidos para la tabla Pedidos y otro llamado MalasHorasDetallePedidos para la tabla DetallePedidos. Ambos deben impedir entre las 11:00 AM y las 12:00 AM la modificación (por inserción, actualización o borrado) de sus respectivas tablas y visualizar un mensaje de advertencia.

CREATE OR REPLACE TRIGGER MalasHorasPedidos
BEFORE DELETE OR INSERT OR UPDATE ON pedidos
BEGIN
  IF (TO_CHAR(SYSDATE, 'HH24') IN ('11')) THEN
    RAISE_APPLICATION_ERROR(-20001, 'No se pueden realizar modificaciones entre las 11:00AM y las 12:00AM');
  END IF;
END;

Modificación de Triggers para Especificar el Tipo de Operación Impedida

Ejercicio 2: Modifica los triggers y/o procedimientos anteriores para que el mensaje de advertencia especifique claramente que se ha impedido la inserción, el borrado o la actualización.

CREATE OR REPLACE TRIGGER MalasHorasPedidos
BEFORE DELETE OR UPDATE OR INSERT ON pedidos
BEGIN
  IF DELETING AND TO_CHAR(SYSDATE, 'HH24') IN ('11') THEN
    RAISE_APPLICATION_ERROR(-20001, 'No se puede borrar en la tabla pedidos entre las 11:00AM y las 12:00AM');
  ELSIF UPDATING AND TO_CHAR(SYSDATE, 'HH24') IN ('11') THEN
    RAISE_APPLICATION_ERROR(-20001, 'No se puede actualizar en la tabla pedidos entre las 11:00AM y las 12:00AM');
  ELSIF INSERTING AND TO_CHAR(SYSDATE, 'HH24') IN ('11') THEN
    RAISE_APPLICATION_ERROR(-20001, 'No se puede insertar en la tabla pedidos entre las 11:00AM y las 12:00AM');
  END IF;
END;

Trigger para Actualizar el Representante de Ventas al Borrar un Empleado

Ejercicio 3: Crea un trigger llamado BorraEmpleado de tal manera que al borrar un empleado, los clientes que lo tuviesen como representante de ventas pasen a tener a su jefe (Comprueba que funciona borrando un empleado que sea del tipo Representante de Ventas).

CREATE OR REPLACE TRIGGER BorraEmpleado
AFTER DELETE ON empleados
FOR EACH ROW
BEGIN
  UPDATE clientes
  SET codigoempleadorepventas = :old.codigojefe
  WHERE codigoempleadorepventas = :old.codigoempleado;
END;

DELETE FROM empleados WHERE codigoempleado = 21;

Modificación del Trigger para Evitar Borrar Jefes con Subordinados

Ejercicio 4: Comprueba que el trigger anterior no funciona intentando borrar un empleado que sea jefazo (Director o subdirector). Intenta modificarlo para solucionar el problema.

CREATE OR REPLACE TRIGGER BorraEmpleado2
BEFORE DELETE ON Empleados
FOR EACH ROW
DECLARE
  subordinados INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO subordinados FROM empleados WHERE codigojefe = :old.codigoempleado;
  IF subordinados = 0 THEN
    UPDATE clientes
    SET codigoempleadorepventas = :old.codigojefe
    WHERE codigoempleadorepventas = :old.codigoempleado;
  ELSE
    RAISE_APPLICATION_ERROR(-20000, 'No se puede borrar porque tiene subordinados');
  END IF;
END;