sql server - una - Mantenimiento del registro de auditoría para entidades divididas en varias tablas

sql server auditoria de tablas

Question

Tenemos una entidad dividida en 5 tablas diferentes. Los registros en 3 de esas tablas son obligatorios. Los registros en las otras dos tablas son opcionales (basados ​​en el subtipo de entidad).

Una de las tablas se designa como entidad principal. Los registros en las otras cuatro tablas están codificados por la identificación única del maestro.

Después de que el disparador de actualización / eliminación esté presente en cada tabla y el cambio de un registro guarda el historial (desde la tabla eliminada dentro del desencadenante) en una tabla de historial relacionada. Cada tabla de historial contiene campos de entidades relacionadas + una marca de tiempo.

Por lo tanto, los registros en vivo siempre están en las tablas en vivo y el historial / cambios están en las tablas de historial. Los registros históricos se pueden ordenar en función de la columna de marca de tiempo. Obviamente, las columnas de marca de tiempo no están relacionadas en las tablas de historial.

Ahora, para la parte más difícil.

1. Los registros se insertan inicialmente en una sola transacción. Se escribirán 3 o 5 registros en una sola transacción.
2. Las actualizaciones individuales pueden suceder a cualquiera o a todas las 5 tablas.
3. Todos los registros se actualizan como parte de una sola transacción. Nuevamente, se actualizarán 3 o 5 registros en una sola transacción.
4. El número 2 se puede repetir varias veces.
5. El número 3 se puede repetir varias veces.

Se supone que la aplicación debe mostrar una lista de entradas de puntos en el historial de tiempo basadas en registros escritos solo como transacciones individuales (puntos 1,3 y 5 solamente)

Actualmente estoy teniendo problemas con un algoritmo que recuperará registros históricos basados ​​solo en los datos de la marca de tiempo.

Agregar una tabla HISTORYMASTER para contener la información adicional sobre las transacciones parece abordar parcialmente el problema. Se agrega un nuevo registro a HISTORYMASTER antes de cada transacción. El nuevo HISTORYMASTER.ID se guarda en cada tabla de entidad durante una transacción. El historial de puntos en el tiempo se puede recuperar seleccionando el primer registro para un HISTORYMASTER.ID en particular (ordenado por timestamp)

¿Hay alguna forma más óptima de administrar tablas de auditoría basadas en DESPLAZAR DESPUÉS (ACTUALIZAR, ELIMINAR) para entidades que abarcan varias tablas?

Answer 1

Su HistoryMaster parece similar a cómo hemos abordado el historial de múltiples elementos relacionados en uno de nuestros sistemas. Al tener un único punto para colgar todos los cambios relacionados en la tabla de historial, es fácil crear una vista que use el maestro de historial como el centro y adjunte la información relacionada. También le permite no crear registros en el historial donde no se desea una auditoría.

En nuestro caso, las tablas principales se llamaron EntityAudit (donde la entidad era el elemento "primario" que se conserva) y todos los datos se almacenaron en las tablas de EntityHistory relacionadas con la auditoría. En nuestro caso, estábamos usando una capa de datos para las reglas de negocios, por lo que fue fácil insertar las reglas de auditoría en la propia capa de datos. Siento que la capa de datos es un punto óptimo para dicho seguimiento si y solo si todas las modificaciones usan esa capa de datos. Si tiene varias aplicaciones que usan distintas capas de datos (o ninguna), entonces sospecho que un desencadenador que crea el registro maestro es prácticamente el único camino a seguir.

Si no tiene información adicional para rastrear en la auditoría (rastreamos al usuario que realizó el cambio, por ejemplo, algo que no está en las tablas principales) entonces consideraría poner la identificación de auditoría adicional en el registro "principal". Tu descripción no parece indicar que estés interesado en los cambios menores en las tablas individuales, sino solo en los cambios que actualizan todo el conjunto de entidades (aunque puedo pasar por alto eso). Solo lo haría si no te importan las ediciones menores. En nuestro caso, necesitábamos rastrear todos los cambios, incluso a los registros relacionados.

Tenga en cuenta que el uso de una tabla Audit / Master tiene la ventaja de que realiza cambios mínimos en las tablas History en comparación con las tablas fuente: un AuditID único (en nuestro caso, un Guid, aunque los autonumeres estarían bien en los no distribuidos) bases de datos).

Answer 2

¿Puede agregar una columna de tipo de datos TimeStamp / RowVersion a la tabla principal de entidades y asociar todos los registros de auditoría con eso?

Pero una actualización de cualquiera de las tablas "secundarias" deberá actualizar la tabla de entidad maestra para forzar el cambio de TimeStamp / RowVersion :(

O inserte un GUID que refresque cada vez que cambie uno de los registros asociados.

Pensando en esto, en voz alta, puede ser mejor tener una tabla unida a la Entidad maestra 1: 1 que solo contenga la ID de entidad maestra y el "número de versión" para el registro - TimeSTamp / RowVersion, GUID, número incrementado o algo más.

Answer 3

Creo que es un síntoma de intentar capturar eventos de auditoría "abstractos" en el nivel más bajo de la pila de aplicaciones: la base de datos.

Si es posible, considere atrapar los eventos de auditoría en su capa empresarial. Esto le permitiría capturar el historial por transacción lógica en lugar de hacerlo fila por fila. La fecha / hora no es confiable para resolver cosas como esta ya que puede ser diferente para filas diferentes, y lo mismo para transacciones concurrentes (o espaciadas de cerca).

Aunque entiendo que me has preguntado cómo hacer esto en los desencadenadores DB. No sé sobre SQL Server, pero en Oracle puede superar esto utilizando el paquete de sistema DBMS_TRANSACTION.LOCAL_TRANSACTION_ID para devolver la identificación de la transacción actual. Si puede recuperar un valor de SQLServer equivalente, puede usar esto para vincular las actualizaciones de registros para la transacción actual en un paquete lógico.