sql - una - crear usuario db2 linux

Crear desencadenante de tabla de historial de DB2

Question

Quiero crear una tabla de historial para rastrear los cambios de campo en varias tablas en DB2.

Sé que el historial generalmente se hace copiando la estructura de una tabla completa y dándole un nombre con sufijo (por ejemplo, usuario -> user_history). Luego puede usar un disparador bastante simple para copiar el registro anterior en la tabla de historial en una ACTUALIZACIÓN.

Sin embargo, para mi aplicación esto usaría demasiado espacio. No parece una buena idea (al menos para mí) copiar un registro completo a otra tabla cada vez que cambia un campo. Así que pensé que podría tener una tabla genérica de "historial" que rastrearía los cambios de campo individuales:

CREATE TABLE history
(
    history_id LONG GENERATED ALWAYS AS IDENTITY,
    record_id INTEGER NOT NULL,
    table_name VARCHAR(32) NOT NULL,
    field_name VARCHAR(64) NOT NULL,
    field_value VARCHAR(1024),
    change_time TIMESTAMP,
    PRIMARY KEY (history_id)
);

De acuerdo, entonces cada tabla que quiero rastrear tiene un solo campo de ID generado automáticamente como la clave principal, que se pondría en el campo ''record_id''. Y el tamaño máximo de VARCHAR en las tablas es 1024. Obviamente, si un campo que no es VARCHAR cambia, tendría que convertirse en un VARCHAR antes de insertar el registro en la tabla de historial.

Ahora bien, esta podría ser una forma completamente retrasada de hacer las cosas (hey, déjame saber por qué si es así), pero creo que es una buena forma de rastrear los cambios que deben retirarse raramente y deben almacenarse para un tiempo significativo. cantidad de tiempo.

De todos modos, necesito ayuda para escribir el desencadenador para agregar registros a la tabla de historial en una actualización. Tomemos, por ejemplo, una tabla hipotética de usuarios:

CREATE TABLE user
(
   user_id INTEGER GENERATED ALWAYS AS IDENTITY,
   username VARCHAR(32) NOT NULL,
   first_name VARCHAR(64) NOT NULL,
   last_name VARCHAR(64) NOT NULL,
   email_address VARCHAR(256) NOT NULL
   PRIMARY KEY(user_id)
);

Entonces, ¿alguien puede ayudarme con un disparador en una actualización de la tabla de usuarios para insertar los cambios en la tabla de historial? Supongo que será necesario utilizar algún SQL de procedimiento para recorrer los campos en el registro anterior, compararlos con los campos del nuevo registro y, si no coinciden, agregar una nueva entrada en la tabla de historial.

Sería preferible usar la misma acción de activación SQL para cada tabla, independientemente de sus campos, si es posible.

¡Gracias!

Answer 1

¿Has considerado hacer esto como un proceso de dos pasos? Implemente un disparador simple que registre la versión original y modificada de toda la fila. Luego, escriba un programa por separado que se ejecute una vez al día para extraer los campos modificados como lo describió anteriormente.

Esto hace que el desencadenador sea más simple, más seguro, más rápido y tiene más opciones sobre cómo implementar el paso de procesamiento posterior.

Answer 2

No creo que sea una buena idea, ya que genera aún más sobrecostos por valor con una gran tabla donde cambia más de un valor. Pero eso depende de tu aplicación.

Además, debe considerar el valor práctico de dicha tabla de historial. Tienes que juntar muchas filas para tener una idea del contexto del valor cambiado y te pide que codifiques otra aplicación que hace solo esta compleja lógica de historial para un usuario final. Y para un administrador de base de datos sería engorroso restablecer valores fuera de la historia.

puede sonar un poco duro, pero esa no es la intención. Un programador experimentado en nuestra tienda tuvo una idea similar a través del diario de tabla. Lo puso en marcha, pero se comió en el espacio de disco como si no hubiera un mañana.

Solo piense en lo que su tabla de historia realmente debería lograr.

Answer 3

Hacemos algo similar en nuestra base de datos SQL Server, pero las tablas de auditoría son para cada tabla individual auditada (una tabla central sería enorme ya que nuestra base de datos tiene muchos gigabytes de tamaño)

Una cosa que debes hacer es asegurarte de registrar también quién hizo el cambio. También debe registrar el valor anterior y el nuevo juntos (hace que sea más fácil volver a poner los datos si es necesario) y el tipo de cambio (insertar, actualizar, eliminar). No menciona la eliminación de grabaciones de la tabla, pero consideramos que esas son algunas de las cosas con las que más utilizamos la tabla.

Usamos SQl dinámico para generar el código para crear las tablas de auditoría (utilizando la tabla que almacena la información del sistema) y todas las tablas de auditoría tienen la misma estructura (hace que las copias sean más fáciles de recuperar datos).

Cuando crea el código para almacenar los datos en su tabla de historial, cree también el código para restaurar los datos si es necesario. Esto ahorrará toneladas de tiempo en el camino cuando algo necesita ser restaurado y usted está bajo la presión de la gerencia para que lo haga ahora.

Ahora no sé si planeaba restaurar los datos de su tabla de historial, pero una vez que lo haya hecho una vez, puedo garantizar que la administración lo desee de esa manera.

Answer 4

CREATE TABLE HIST.TB_HISTORY ( 
    HIST_ID     BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1, NO CACHE) NOT NULL,
    HIST_COLUMNNAME     VARCHAR(128) NOT NULL,
    HIST_OLDVALUE       VARCHAR(255),
    HIST_NEWVALUE       VARCHAR(255),
    HIST_CHANGEDDATE    TIMESTAMP NOT NULL
    PRIMARY KEY(HIST_SAFTYNO)
)
GO


CREATE TRIGGER COMMON.TG_BANKCODE AFTER
UPDATE OF FRD_BANKCODE ON COMMON.TB_MAINTENANCE
REFERENCING OLD AS oldcol NEW AS newcol FOR EACH ROW MODE DB2SQL
WHEN(COALESCE(newcol.FRD_BANKCODE,''#null#'') <> COALESCE(oldcol.FRD_BANKCODE,''#null#''))
BEGIN ATOMIC

    CALL FB_CHECKING.SP_FRAUDHISTORY_ON_DATACHANGED(
                newcol.FRD_FRAUDID,
                ''FRD_BANKCODE'',
                oldcol.FRD_BANKCODE,
                newcol.FRD_BANKCODE,
                newcol.FRD_UPDATEDBY
    );--

    INSERT INTO FB_CHECKING.TB_FRAUDMAINHISTORY(        
        HIST_COLUMNNAME, 
        HIST_OLDVALUE, 
        HIST_NEWVALUE, 
        HIST_CHANGEDDATE