sql server - usar - Tablas de historial pros, contras y errores: uso de desencadenantes, sproc o a nivel de aplicación

sql sp_helptext (6)

Actualmente estoy jugando con la idea de tener tablas de historial para algunas de mis tablas en mi base de datos. Básicamente, tengo la tabla principal y una copia de esa tabla con una fecha modificada y una columna de acción para almacenar qué acción se realizó, por ejemplo, Actualizar, Eliminar e Insertar.

Hasta ahora puedo pensar en tres lugares diferentes en los que puedes hacer el trabajo de la tabla de historial.

Se activan en la tabla principal para actualizar, insertar y eliminar. (Base de datos)
Procedimientos almacenados. (Base de datos)
Capa de aplicación. (Solicitud)

Mi pregunta principal es, ¿cuáles son los pros, los contras y las consecuencias de hacer el trabajo en cada una de estas capas?

Una de las ventajas que se me ocurren al utilizar los activadores es que la integridad siempre se mantiene sin importar qué programa se implemente sobre la base de datos.

Generalmente, si elige la capa de aplicación, puede diseñar el código de su aplicación para hacer el registro en un solo punto, que manejará consistentemente toda su tabla histórica. los disparadores de manera diferente son un enfoque más complicado de mantener porque se replican (según la tecnología de db) para cada tabla: en el caso de cientos de tablas, la cantidad de código para el disparador puede ser un problema.

Si tiene una organización de soporte que mantendrá el código que está escribiendo ahora y no sabe quién mantendrá su código (típico para las grandes industrias), no puede asumir cuál es el nivel de habilidad de la persona que lo solucionará. aplicación, en ese caso es mejor en mi opinión hacer que el principio de funcionamiento de la tabla histórica sea lo más simple posible, y la capa de aplicación es probablemente el mejor lugar para este propósito.

Ha estado utilizando el enfoque basado en el activador durante años y definitivamente nos ha funcionado bien, pero luego tiene los siguientes puntos sobre los que reflexionar:

Los desencadenadores en un uso intensivo (por ejemplo, una aplicación basada en SaaS de varios inquilinos) podrían ser extremadamente costosos
En algunos escenarios, algunos campos pueden ser redundantes. Los desencadenadores son buenos solo cuando está claro en los campos a registrar; aunque utilizando una aplicación, podría tener una capa de interceptor que podría ayudarlo a registrar ciertos campos según la "configuración"; aunque con su propia cuota de gastos generales
Sin un control adecuado de la base de datos, una persona podría deshabilitar fácilmente los disparadores, modificar los datos y habilitar los disparadores; Todo sin alarmar ninguna alarma.
En el caso de las aplicaciones web, donde las conexiones se establecen desde un grupo, el seguimiento de los usuarios reales que realizaron los cambios puede ser tedioso. Una posible solución sería tener el campo "EditedBy" en cada tabla de transacciones.

Lo pondría de esta manera:

Procs almacenados: se omiten si modifica la tabla directamente. La seguridad en la base de datos puede controlar esto
Aplicación: mismo trato. Además, si tiene varias aplicaciones, posiblemente en diferentes idiomas, debe implementarse en cada pila, que es algo redundante; y
Disparadores: transparentes a la aplicación y capturarán todos los cambios. Este es mi método preferido.

Los disparadores son la única forma confiable de capturar cambios. Si lo hace en Stored Procs o en la aplicación, siempre puede ingresar y eliminar un cambio para el que no tiene un registro (inadvertidamente). Por supuesto, alguien que no quiere dejar un registro puede deshabilitar los activadores. Pero prefiere forzar a alguien a deshabilitar el registro que esperar a que recuerden incluirlo.

Los disparadores son la forma más rápida y fácil de lograr una historia simple. La siguiente información supone un ejemplo más complejo en el que el procesamiento del historial puede incluir algunas reglas de negocios y puede requerir información de registro que no se encuentra en la tabla que se realiza el seguimiento.

Para aquellos que piensan que los desencadenantes son más seguros que los sprocs porque no pueden ser evitados, les recuerdo que están haciendo la siguiente suposición:

!) Existen permisos que impiden que los usuarios ejecuten DISABLE TRIGGER [pero luego los permisos también podrían existir para limitar todo el acceso a la base de datos, excepto EXECUTE en sprocs, que es un patrón común para aplicaciones empresariales] - por lo tanto, uno debe asumir los permisos correctos y, por lo tanto, igual se dispara en términos de seguridad y capacidad de ser anulado

!) Dependiendo de la base de datos, es posible ejecutar instrucciones de actualización que no activen disparadores. Podría aprovechar el conocimiento de la profundidad de ejecución del desencadenador anidado para omitir un desencadenante. La única solución segura incluye la seguridad en la base de datos y la limitación del acceso a los datos utilizando solo mecanismos aprobados, ya sean disparadores, sprocs o capas de acceso a datos.

Creo que las opciones son claras aquí. Si varias aplicaciones acceden a los datos, entonces usted quiere controlar el historial desde la capa común más baja y esto significará la base de datos.

Siguiendo la lógica anterior, la elección de activadores o procedimientos almacenados depende nuevamente de si el procedimiento almacenado es la capa común más baja. Debería preferir el efecto sobre el gatillo, ya que puede controlar mejor el rendimiento, los efectos secundarios y el código es más fácil de mantener.

Los desencadenadores son aceptables, pero trate de asegurarse de no aumentar los bloqueos leyendo los datos fuera de las tablas que se están actualizando. Limite los desencadenantes a inserciones en las tablas de registro, registre solo lo que necesita.

Si la aplicación utiliza una capa de acceso lógico común y es poco probable que esto cambie con el tiempo, preferiría implementar la lógica aquí. Use un patrón de Cadena de responsabilidad y una arquitectura de plug-in, conduzca esto desde Dependency Injection para permitir todo tipo de procesamiento en su módulo de historial, incluido el registro a diferentes tipos de tecnología, diferentes bases de datos, un servicio de historial o cualquier otra cosa que desee. podría imaginar

Una tarde, pero agrega un par de opciones más que pueden ser consideradas.

Cambiar captura de datos: This función está disponible en SQL Server 2008 R2 + pero solo en la edición empresarial. Le permite seleccionar las tablas que desea rastrear y SQL Server hará el trabajo por usted. Funciona leyendo el registro de transacciones y llenando las tablas de historial con datos.

Leyendo el registro de transacciones: si la base de datos está en modo de recuperación completa, se puede leer el registro de transacciones y se pueden encontrar detalles sobre casi las transacciones.

El inconveniente es que esto no es compatible de forma predeterminada. Las opciones son leer el registro de transacciones usando funciones no documentadas como fn_dblog o herramientas de terceros como ApexSQL Log .

Desencadenadores: funciona bien para una pequeña cantidad de tablas en las que no hay demasiados desencadenantes para administrar. Si tiene muchas tablas que desea auditar, debería considerar alguna herramienta de terceros para esto.

Todo esto funciona a nivel de base de datos y es completamente transparente a la aplicación.