Saltar a contenido

BD2 · S2 · Triggers DML y auditoría

SGE-JD · JD Refrigeración S.A.C. · Equipo CherryTree · Yoel Wagner Chambi Sirena · 10/11/2026

Trabajo autónomo de la sesión 2: una tabla de auditoría y dos triggers sobre la entidad transaccional del proyecto, la venta (VENTAS). Uno aplica una regla de negocio (BEFORE) y el otro registra cada cambio (AFTER). Se ejecutó en la base de práctica sge-jd-bd2, después de S1.

Orden Script Qué hace
1 S02_01_auditoria.sql Tabla VENTA_AUDITORIA (sin FK) y su índice
2 S02_02_triggers.sql TRG_VENTA_ESTADO_BU (BEFORE UPDATE OF estado) y TRG_VENTA_AUDITORIA (AFTER INSERT OR UPDATE OR DELETE)
3 S02_03_pruebas.sql Dos casos válidos, tres rechazados y la consulta de la auditoría; se puede repetir
.\bd2\herramientas\sql.ps1 bd2\S02\S02_01_auditoria.sql
.\bd2\herramientas\sql.ps1 bd2\S02\S02_02_triggers.sql
.\bd2\herramientas\sql.ps1 bd2\S02\S02_03_pruebas.sql

1. Regla de negocio y alcance de la auditoría

Elemento SGE-JD
Tabla protegida VENTAS
Regla automática (BEFORE) Una venta ANULADA es definitiva: no vuelve a REGISTRADA. Para anular hay que decir por qué (motivo de 5 caracteres o más) y quién. Si no se envía la fecha de anulación, la pone la base
Operaciones auditadas INSERT, UPDATE y DELETE sobre VENTAS
Datos por evento Venta, acción, estado y estado de pago antes y después (:OLD/:NEW), cliente, quién anuló y motivo, fecha y hora, usuario Oracle, programa y usuario del sistema operativo

Por qué es una regla real: al anular, VentaService.anular() devuelve el stock y Facturación emite la nota de crédito. Si alguien regresara la venta a REGISTRADA con un UPDATE, el stock quedaría contado dos veces y la nota de crédito anularía una venta que figura como vigente. En Java esa regla ya existía: "ya está ANULADA" responde 409, el motivo es obligatorio y el usuario viene del token. Hasta ahora solo Java la cuidaba.

Por qué no basta una CHECK. Una CHECK solo ve la fila nueva. CHECK (estado IN ('REGISTRADA','ANULADA')) acepta las dos, pero no sabe de dónde viene la fila. "No volver de ANULADA a REGISTRADA" compara el valor anterior con el nuevo, y eso solo lo puede hacer un trigger con :OLD y :NEW.

2. Tabla de auditoría

CREATE TABLE venta_auditoria (
    id_auditoria          NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id_venta              NUMBER        NOT NULL,       -- sin FK a propósito
    accion                VARCHAR2(10)  NOT NULL,
    estado_anterior       VARCHAR2(20),
    estado_nuevo          VARCHAR2(20),
    estado_pago_anterior  VARCHAR2(20),
    estado_pago_nuevo     VARCHAR2(20),
    cliente_id            NUMBER,
    anulado_por           VARCHAR2(100),                 -- usuario de Keycloak que guardó el backend
    motivo                VARCHAR2(300),
    fecha_evento          TIMESTAMP     DEFAULT SYSTIMESTAMP NOT NULL,
    usuario_oracle        VARCHAR2(128) DEFAULT USER NOT NULL,
    programa              VARCHAR2(64),                  -- sqlplus, JDBC Thin Client, SQL Developer…
    usuario_so            VARCHAR2(128),
    CONSTRAINT ck_venta_auditoria_accion CHECK (accion IN ('INSERT', 'UPDATE', 'DELETE'))
);
CREATE INDEX ix_venta_auditoria_venta ON venta_auditoria (id_venta, fecha_evento);

ID_VENTA no tiene FOREIGN KEY hacia VENTAS. Con FK, Oracle no dejaría borrar una venta auditada (ORA-02292), y si se usara ON DELETE CASCADE, borrar la venta borraría también su historia. Se comprobó en la prueba: la limpieza borró las ventas 1016 y 1017, y sus filas de auditoría siguen ahí. El índice por venta y fecha sirve para la consulta habitual, "qué le pasó a la venta X".

3. Trigger de regla de negocio (BEFORE)

CREATE OR REPLACE TRIGGER trg_venta_estado_bu
BEFORE UPDATE OF estado ON ventas
FOR EACH ROW
BEGIN
    IF :OLD.estado = 'ANULADA' AND :NEW.estado <> 'ANULADA' THEN
        RAISE_APPLICATION_ERROR(-20021,
            'La venta ' || :OLD.id || ' está ANULADA y no puede volver a ' || :NEW.estado || '.');
    END IF;
    IF :OLD.estado = 'REGISTRADA' AND :NEW.estado = 'ANULADA' THEN
        IF LENGTH(TRIM(:NEW.motivo_anulacion)) IS NULL OR LENGTH(TRIM(:NEW.motivo_anulacion)) < 5 THEN
            RAISE_APPLICATION_ERROR(-20022,
                'Para anular la venta ' || :OLD.id || ' indique el motivo (al menos 5 caracteres).');
        END IF;
        IF :NEW.anulado_por IS NULL THEN
            RAISE_APPLICATION_ERROR(-20023, 'Para anular la venta ' || :OLD.id || ' indique quién la anula.');
        END IF;
        :NEW.fecha_anulacion := NVL(:NEW.fecha_anulacion, TRUNC(SYSDATE));
    END IF;
END;

Es BEFORE por dos razones. Si la regla falla, la fila nunca se escribe, así que no hay nada que deshacer y el trigger AFTER ni se dispara. Y solo en BEFORE se puede asignar :NEW, como hace la línea que completa FECHA_ANULACION. UPDATE OF estado hace que la regla corra solo cuando la sentencia toca el estado. Un cambio de estado de pago desde Cobranza no la activa.

Casos rechazados (salida real):

UPDATE ventas SET estado = 'REGISTRADA' WHERE id = :v_venta
ORA-20021: La venta 1018 está ANULADA y no puede volver a REGISTRADA.
ORA-06512: at "JDREFRIGERACION.TRG_VENTA_ESTADO_BU", line 4

UPDATE ventas SET estado = 'ANULADA', anulado_por = 'lucy' WHERE id = :v_venta2
ORA-20022: Para anular la venta 1019 indique el motivo (al menos 5 caracteres).

UPDATE ventas SET estado = 'ANULADA', motivo_anulacion = 'Error de digitación' WHERE id = :v_venta2
ORA-20023: Para anular la venta 1019 indique quién la anula.

4. Trigger de auditoría (AFTER)

CREATE OR REPLACE TRIGGER trg_venta_auditoria
AFTER INSERT OR UPDATE OR DELETE ON ventas
FOR EACH ROW
DECLARE
    v_accion venta_auditoria.accion%TYPE;
BEGIN
    IF INSERTING THEN v_accion := 'INSERT';
    ELSIF UPDATING THEN v_accion := 'UPDATE';
    ELSE v_accion := 'DELETE';
    END IF;
    INSERT INTO venta_auditoria (id_venta, accion, estado_anterior, estado_nuevo,
                                 estado_pago_anterior, estado_pago_nuevo, cliente_id,
                                 anulado_por, motivo, programa, usuario_so)
    VALUES (COALESCE(:NEW.id, :OLD.id), v_accion, :OLD.estado, :NEW.estado,
            :OLD.estado_pago, :NEW.estado_pago, COALESCE(:NEW.cliente_id, :OLD.cliente_id),
            COALESCE(:NEW.anulado_por, :OLD.anulado_por), COALESCE(:NEW.motivo_anulacion, :OLD.motivo_anulacion),
            SUBSTR(SYS_CONTEXT('USERENV', 'MODULE'), 1, 64), SYS_CONTEXT('USERENV', 'OS_USER'));
END;

De dónde sale cada dato:

Dato INSERT UPDATE DELETE
id_venta :NEW.id :NEW.id :OLD.id (con COALESCE)
Estado y pago anterior nulo (no había fila) :OLD :OLD
Estado y pago nuevo :NEW :NEW nulo (ya no hay fila)
Quién, programa, fecha DEFAULT USER, SYS_CONTEXT, SYSTIMESTAMP igual igual

Es AFTER porque registra lo que sí pasó. Si un BEFORE rechaza la sentencia, el AFTER no se dispara y la auditoría no guarda intentos que nunca ocurrieron.

5. Evidencia de ejecución

Compilación:

Table created. · Index created. · Trigger created. No errors. · Trigger created. No errors.
TRG_VENTA_AUDITORIA  AFTER EACH ROW   INSERT OR UPDATE OR DELETE  ENABLED
TRG_VENTA_ESTADO_BU  BEFORE EACH ROW  UPDATE                      ENABLED

Caso válido. Se registran dos ventas con SP_REGISTRAR_VENTA de S1, y la 1018 se anula como lo hace el backend: devuelve el stock y pone estado, quién y motivo. La sentencia no envía la fecha:

  ID ESTADO   FECHA_ANULACION ANULADO_POR MOTIVO_ANULACION
1018 ANULADA  10-NOV-26       lucy        Cliente desistió de la compra
(FECHA_ANULACION no se envió en el UPDATE: la puso el trigger BEFORE)

Auditoría de la corrida. Los tres rechazados no aparecen. Los dos cambios de estado de pago sí, aunque no activaron la regla:

ID_VENTA ACCION ESTADO_ANT ESTADO_NUEVO PAGO_ANT  PAGO_NUEVO ANULADO_POR MOTIVO                         USUARIO_ORACLE  PROGRAMA  FECHA
    1018 INSERT            REGISTRADA             PAGADO                                                JDREFRIGERACION SQL*Plus  10/11 18:54:15
    1019 INSERT            REGISTRADA             PAGADO                                                JDREFRIGERACION SQL*Plus  10/11 18:54:15
    1018 UPDATE REGISTRADA ANULADA      PAGADO    PAGADO     lucy        Cliente desistió de la compra  JDREFRIGERACION SQL*Plus  10/11 18:54:15
    1019 UPDATE REGISTRADA REGISTRADA   PAGADO    PENDIENTE                                             JDREFRIGERACION SQL*Plus  10/11 18:54:15
    1019 UPDATE REGISTRADA REGISTRADA   PENDIENTE PAGADO                                                JDREFRIGERACION SQL*Plus  10/11 18:54:15

FILAS_CON_REGRESO_A_REGISTRADA
                             0

DELETE auditado. La limpieza del inicio borró las ventas de la corrida anterior, y su historia quedó:

ID_VENTA ACCION ESTADO_ANT ESTADO_PAGO_ANTERIOR PROGRAMA
    1016 DELETE ANULADA    PAGADO               SQL*Plus
    1017 DELETE REGISTRADA PAGADO               SQL*Plus
Total en la tabla: DELETE 2 · INSERT 4 · UPDATE 6

6. Error o hallazgo

Hallazgo 1: la base estaba 5 horas adelantada. En la primera corrida la auditoría marcó 10/11 23:53:16, cuando en Lima eran las 18:53. El contenedor Oracle corre en UTC (date dentro del contenedor: 23:53:23 UTC). Esto no es solo cosmético: TRUNC(SYSDATE) de este trigger y de SP_REGISTRAR_VENTA (S1) daría como fecha el día siguiente a cualquier venta o anulación hecha después de las 7 p. m. El contenedor de producción ya tenía TZ: America/Lima en compose-prod.yml, pero el de desarrollo (docker-compose.yml) y el de práctica no. Corrección: se agregó TZ: America/Lima a bd2/compose-bd2.yml y se recreó el contenedor. Ahora SYSDATE sale 10/11 18:54 y la segunda corrida marca 18:54:15. Queda pendiente aplicarlo al de desarrollo al integrar (ver ../README.md). El backend no se afectaba, porque calcula sus fechas en Java con la hora de Windows.

Hallazgo 2: USER no dice quién fue. El backend se conecta siempre como JDREFRIGERACION, así que USUARIO_ORACLE será el mismo para Lisandro, para Lucy y para un script. Por eso la tabla guarda también ANULADO_POR, que es el usuario de Keycloak que escribe el backend, y el programa (SYS_CONTEXT('USERENV', 'MODULE')), que distingue SQL*Plus del backend. Separar el usuario de la aplicación del propietario es tema de S8.

Observación para S3. El trigger no impide borrar una venta anulada (la limpieza lo hace). En el sistema real una venta no se borra, se anula. Un DELETE directo debería rechazarse, salvo el de un script de mantenimiento autorizado. Se decide en S3, junto con el registro de errores.

7. Relación con ADS y LP2

Objeto BD2 Decisión ADS Servicio LP2
TRG_VENTA_ESTADO_BU Integridad: la anulación es definitiva sin importar quién escriba (ADR-S1-02) PATCH /api/v1/admin/ventas/{id}/anular → VentaService.anular(); ahora con una segunda capa en Oracle
TRG_VENTA_AUDITORIA Auditabilidad (atributo 3 de ADS S1) Ninguno directo: el backend no sabe que existe
VENTA_AUDITORIA Trazabilidad de cambios Complementa HISTORIAL_CAMBIOS (cambios de configuración y permisos) y los logs

VentaService.anular() pone estado, fecha, quién y motivo antes de guardar, así que el UPDATE que genera Hibernate ya cumple la regla y el backend no debería necesitar cambios. Se verifica al conectar el backend a esta base, en la integración final.

8. Reflexión

¿Por qué una regla de negocio protegida solo en el backend no es suficiente si otro cliente puede escribir directo en la base de datos?

Porque el backend es solo una de las puertas. En SGE-JD también escriben en Oracle las migraciones, los scripts de limpieza, las restauraciones de respaldos y quien abre SQL Developer para "arreglar" una venta. Ninguno de ellos pasa por VentaService.anular(). Con la regla solo en Java, un UPDATE ventas SET estado = 'REGISTRADA' directo habría revivido una venta anulada, con su stock ya devuelto y su nota de crédito emitida, sin error y sin rastro. Con el trigger, Oracle lo rechaza (ORA-20021) venga de donde venga. Y lo que sí se permite queda en VENTA_AUDITORIA, con el programa que lo hizo. La regla en el backend sigue sirviendo para dar un mensaje claro al usuario. La de la base es la que garantiza que se cumple.