java - una - Historial de auditoría de múltiples tablas en la base de datos

insertar datos en varias tablas mysql java

Question

Tengo 3-4 tablas en mi base de datos para las cuales quiero hacer un seguimiento de los cambios.

Estoy principalmente preocupado por las actualizaciones.

Cada vez que se producen actualizaciones, quiero almacenar la entrada anterior (valor o fila completa) en la tabla de auditoría.

Las columnas básicas en las que estaba pensando son las siguientes:

AuditId, TableName, PK1, PK2, PK3, PKVal1, PKVal2, PKVal3,  UpdateType, PrevEntryJSON

JSON tendrá formato: Key:Value y prefiero ir con él ya que las columnas siguen cambiando y quiero mantener todos los valores, incluso si no cambian.

Otra opción es eliminar JSON con cientos de columnas que tendrán nombres iguales a columnas diferentes (acumulativas de todas las tablas).

Quería escuchar las opiniones de la gente sobre esto. ¿Cómo podría mejorar y qué problemas podría enfrentar?

Pasar por los desencadenadores podría no ser una opción preferible, pero estoy abierto a ello.

Gracias,

Answer 1

He visto una implementación muy efectiva de esto que dice lo siguiente:

TABLE audit_entry (
    audit_entry_id          INTEGER         PRIMARY KEY,
    audit_entry_type        VARCHAR2(10)    NOT NULL,
    -- ^^ stores ''INSERT'' / ''UPDATE'' -- / ''DELETE''

    table_name              VARCHAR2(30)    NOT NULL,
    -- ^^ stores the name of the table that is changed

    column_name             VARCHAR2(30)    NOT NULL,
    -- ^^ stores the name of the column that is changed

    primary_key_id          INTEGER         NOT NULL,
    -- ^^ Primary key ID to identify the row that is changed

    -- Below are the actual values that are changed.
    -- If the changed column is a foreign key ID then
    -- below columns tell you which is new and which is old
    old_id                  INTEGER,
    new_id                  INTEGER,

    -- If the changed column is of any other numeric type,
    -- store the old and new values here.
    -- Modify the precision and scale of NUMBER as per your 
    -- choice.
    old_number              NUMBER(18,2),
    new_number              NUMBER(18,2),

    -- If the changed column is of date type, with or without
    -- time information, store it here.
    old_ts                  TIMESTAMP,
    new_ts                  TIMESTAMP,

    -- If the changed column is of VARCHAR2 type,
    -- store it here.
    old_varchar             VARCHAR2(2000),
    new_varchar             VARCHAR2(2000),
    ...
    ... -- Any other columns to store data of other types,
    ... -- e.g., blob, xmldata, etc.
    ...
)

Y creamos una secuencia simple para darnos un nuevo valor entero incremental para audit_entry_id :

CREATE SEQUENCE audit_entry_id_seq;

La belleza de una tabla como audit_entry es que puede almacenar información sobre todos los tipos de DML: INSERT , UPDATE y DELETE en el mismo lugar.

Por ejemplo, para insertar, mantenga old_* columnas old_* y new_* con sus valores.

Para las actualizaciones, new_* columnas old_* y new_* cada vez que se new_* .

Para eliminar, old_* las old_* columnas old_* y conserve new_* null.

Y, por supuesto, ingrese el valor apropiado para audit_entry_type . ; 0)

Entonces, por ejemplo, tienes una tabla como la siguiente:

TABLE emp (
    empno           INTEGER,
    ename           VARCHAR2(100) NOT NULL,
    date_of_birth   DATE,
    salary          NUMBER(18,2) NOT NULL,
    deptno          INTEGER -- FOREIGN KEY to, say, department
    ...
    ... -- Any other columns that you may fancy.
    ...
)

Simplemente crea un disparador en esta tabla de la siguiente manera:

CREATE OR REPLACE TRIGGER emp_rbiud
-- rbiud means Row level, Before Insert, Update, Delete
BEFORE INSERT OR UPDATE OR DELETE
ON emp
REFERENCING NEW AS NEW OLD AS OLD
DECLARE
    -- any variable declarations that deem fit.
BEGIN
    WHEN INSERTING THEN
        -- Of course, you will insert empno.
        -- Let''s populate other columns.

        -- As emp.ename is a not null column, 
        -- let''s insert the audit entry value directly.
        INSERT INTO audit_entry(audit_entry_id,
                                audit_entry_type,
                                table_name,
                                column_name,
                                primary_key,
                                new_varchar)
        VALUES(audit_entry_id_seq.nextval,
               ''INSERT'',
               ''EMP'',
               ''ENAME'',
               :new.empno,
               :new.ename);

        -- Now, as date_of_birth may contain null, we do:
        IF :new.date_of_birth IS NOT NULL THEN
            INSERT INTO audit_entry(audit_entry_id,
                                    audit_entry_type,
                                    table_name,
                                    column_name,
                                    primary_key,
                                    new_ts)
            VALUES(audit_entry_id_seq.nextval,
                   ''INSERT'',
                   ''EMP'',
                   ''DATE_OF_BIRTH'',
                   :new.empno,
                   :new.date_of_birth);
        END IF;

        -- Similarly, code DML statements for auditing other values
        -- as per your requirements.

    WHEN UPDATING THEN
        -- This is a tricky one.
        -- You must check which columns have been updated before you
        -- hurry into auditing their information.

        IF :old.ename != :new.ename THEN
            INSERT INTO audit_entry(audit_entry_id,
                                    audit_entry_type,
                                    table_name,
                                    column_name,
                                    primary_key,
                                    old_varchar,
                                    new_varchar)
            VALUES(audit_entry_id_seq.nextval,
                   ''INSERT'',
                   ''EMP'',
                   ''ENAME'',
                   :new.empno,
                   :old.ename,
                   :new.ename);
        END IF;

        -- Code further DML statements in similar fashion for other
        -- columns as per your requirement.

    WHEN DELETING THEN
        -- By now you must have got the idea about how to go about this.
        -- ;0)
END;
/

Solo una palabra de advertencia: sea selectivo con las tablas y columnas que elija para auditar, porque de todos modos, esta tabla tendrá una gran cantidad de filas. SELECT instrucciones SELECT en esta tabla serán más lentas de lo que cabría esperar.

Realmente me encantaría ver otro tipo de implementación aquí, ya que sería una buena experiencia de aprendizaje. Espero que su pregunta obtenga más respuestas, ya que esta es la mejor implementación de una tabla de auditoría que he visto y todavía estoy buscando formas de mejorarla.