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.