transacciones - Seguimiento de cambios en una base de datos de SQL Server 2005

seguimiento de cambios sql server

Question

Me han encomendado desarrollar una solución que rastree los cambios en una base de datos.

Para las actualizaciones que necesito capturar:

• fecha de actualización
• valor antiguo
• nuevo valor
• campo afectado
• persona haciendo cambio
• ID de registro
• el registro de la mesa está en

Para eliminar:

• fecha de eliminación
• persona haciendo borrar
• El título / descripción / identificación del registro eliminado. Las tablas que estoy rastreando cambian en todas tienen un título o campo de descripción. Me gustaría capturar esto antes de que se elimine el registro.
• el registro de la mesa estaba en

Para inserciones:

• fecha de inserción
• persona haciendo cambio
• ID de registro
• el registro de la mesa está en

He pensado en algunas maneras de hacer esto:

• Estoy usando procedimientos almacenados para cualquier actualización / eliminación / inserción. Crearía una tabla genérica de "seguimiento". Tendría suficientes campos para capturar todos los datos. Luego agregaría otra línea en cada proceso almacenado al efecto de "Insertar registro en la tabla de seguimiento".
  • inconveniente: todas las actualizaciones / eliminaciones / inserciones están todas mezcladas en la misma tabla
  • muchos campos NULLed
  • ¿Cómo rastreo las actualizaciones / eliminaciones / inserciones por lotes? <---- esto podría no ser un problema. Realmente no hago nada como esto en la aplicación.
  • ¿Cómo capturo al usuario que realiza la actualización? La base de datos solo ve una cuenta.
  • editar una gran cantidad de código existente para editar.
• Por último, podría crear un disparador que se llame después de las actualizaciones / eliminaciones / inserciones. Muchas de las desventajas son las mismas que la primera solución, excepto: Tendría que editar la mayor cantidad de código. No estoy seguro de cómo realizar un seguimiento de las actualizaciones. No parece que exista una forma de utilizar activadores para ver los registros actualizados recientemente.

Estoy usando asp.net, C #, sql server 2005, iis6, windows 2003. No tengo un presupuesto tan triste que no puedo comprar nada para ayudarme con esto.

¡Gracias por tus respuestas!

Answer 1

Una forma en que he visto esto manejado (aunque no lo recomendaría, sinceramente) es manejarlo a través de procedimientos almacenados, pasando el ID de usuario / nombre de usuario / como parámetro. Los procedimientos almacenados llamarían a un procedimiento de registro, que escribió los detalles relevantes en una tabla de registro central.

Aquí es donde se volvió un poco loco, sin embargo ...

Para INSERT / UPDATE, las filas relevantes se almacenaron en la tabla como datos XML una vez que INSERT / UPDATE se hubo completado con éxito. Para DELETE, la fila se almacenó antes de la ejecución DELETE (aunque, de manera realista, podrían haberlo obtenido de la salida de la declaración DELETE, al menos con SQL Server 2005).

Si no recuerdo mal, la tabla solo tenía un par de columnas: ID de usuario, Fecha y hora del registro, Tipo de transacción (I / U / D), datos XML que contenían las filas relevantes, nombre de la tabla y valor de la clave primaria (principalmente para búsqueda rápida) de qué registros querían).

Muchas formas de desollar a un gato, aunque ...

Mi consejo es que mantener es simple. Extiéndalo más tarde si / cuando lo necesite.

Si tiene la posibilidad de hacerlo, bloquee a los usuarios para que solo puedan realizar declaraciones accionables en tablas a través de procedimientos almacenados y luego maneje el registro (como lo desee) desde allí.

Answer 2

construimos el nuestro y solo necesitamos que el usuario y la PC pasen a cada procedimiento almacenado de agregar / actualizar. entonces solo se trata de obtener el registro original y llenar las variables y compararlas con las variables aprobadas y registrar los datos en nuestra tabla. para las eliminaciones, solo tenemos una copia de las tablas de origen + un campo de marca de tiempo para que el registro nunca se elimine realmente y pueda restaurarse en cualquier momento que lo necesitemos (obviamente, la rutina de eliminación comprueba las relaciones FK y demás).

add / update log table se ve como datetime, table_name, column_name, record_id, old_value, new_value, user_id, computer

nunca insertamos nulos, por lo que los convertimos en cadenas vacías, las nuevas entradas se marcan con ''{nueva entrada}'' en la columna old_value. record_id se compone de tantas columnas clave para identificar de forma única ese único registro (field1 + ''.'' + field2 + ...)

Answer 3

En primer lugar, en todas sus tablas debe tener al menos estas columnas agregadas a las columnas de datos DateCreated, UserCreated, DateModified, UserModified. Es posible que desee agregar una columna "Estado" o "Última acción" para que nunca elimine realmente una fila que acaba de establecer en un estado eliminado / insertado / actualizado. Luego puede crear una "tabla de historial" que es una copia exacta de la primera tabla. Luego, en las actualizaciones o eliminaciones, el desencadenador copiará las entradas de la tabla Eliminada en la tabla Historial al cambiar los campos DateModified, UserModified y Status al mismo tiempo.

Answer 4

He tenido una configuración en SQL Server donde usaríamos vistas para acceder a nuestros datos, que manejaría inserciones, actualizaciones y eliminaciones con los activadores INSTEAD OF.

Por ejemplo: un activador INSTEAD OF DELETE en la vista marcaría los registros en la tabla subyacente como eliminados, y la vista se filtró para que no se muestren los registros eliminados.

En todos los factores desencadenantes, actualizamos una fecha de modificación y un nombre de usuario. El problema es que registra el nombre de usuario de la base de datos, que no es lo mismo que el nombre de usuario de la aplicación definitiva.

La vista debe estar vinculada al esquema para que esto funcione.

Answer 5

Un desencadenante no tendría toda la información que necesita por varias razones, pero ninguna identificación de usuario es el factor decisivo.

Diría que estás en el camino correcto con un sp común para insertar donde sea que se realice un cambio. Si te estás estandarizando en sp para tus interfaces, entonces estás por delante del juego; será difícil colarse en un cambio que no se rastrea.

Mire esto como el equivalente de una pista de auditoría en una aplicación de contabilidad, este es el Diario, una sola tabla con cada transacción registrada. No implementarían diarios separados para depósitos, retiros, ajustes, etc., y este es el mismo principio.

Answer 6

Odio dejar de lado el tema y sé que no tiene presupuesto, pero la solución más simple será actualizar a SQL Server 2008. Tiene esta característica incorporada . Pensé que al menos debería mencionarse para cualquier otra persona que se encuentre con esta pregunta, incluso si no puede usarla usted mismo.

(Entre las ediciones implementables de SQL 2008, esta característica solo está disponible en Enterprise).

Answer 7

Le sugiero que use 2 columnas en cada mesa. nombres rowhistory e IsDeleted y el tipo de datos será xml y bit. Nunca elimine las filas, siempre use la bandera IsDeleted Now go with update triggers. Le daré ejemplo por lo mismo Tengo esta tabla llamada Página

    CREATE TABLE te_Page([Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](200) NOT NULL, [Description] [varchar](200) NULL,[CreatedBy] [uniqueidentifier] NULL, [CreatedDate] [datetime] NOT NULL, [UpdatedBy] [uniqueidentifier] NULL, [UpdatedDate] [datetime] NULL, [IsDeleted] [bit] NULL, [RowHistory] [xml] NULL, CONSTRAINT [PK_tm_Page] PRIMARY KEY CLUSTERED ([Id] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY]

Ahora, después de crear la tabla, todo lo que necesita hacer es copiar y pegar el siguiente código y su tarea está hecha para la tabla de Página. Comenzará a registrar el historial de la fila en la misma fila que se actualiza junto con los valores antiguos y nuevos.

                ALTER Trigger [dbo].[Trg_Te_Page]    
        On [dbo].[te_Page]                
        After Update                
        As                 
        --If @@rowcount = 0 Or Update(RowHistory)    
        --Return    

        Declare @xml NVARCHAR(MAX)     
        Declare @currentxml NVARCHAR(MAX)   
        Declare @node NVARCHAR(MAX)    
        Declare @ishistoryexists XML    

        Declare @FormLineAttributeValueId int  

        -- new Values  
        Declare @new_Name varchar(200)  
        Declare @new_Description varchar(200)  

        Declare @new_CreatedBy UNIQUEIDENTIFIER    
        Declare @new_CreatedDate DATETIME    
        Declare @new_UpdatedBy UNIQUEIDENTIFIER    
        Declare @new_UpdatedDate DATETIME    
        Declare @new_IsDeleted BIT  

        --old values  
        Declare @old_Name varchar(200)  
        Declare @old_Description varchar(200)  

        Declare @old_CreatedBy UNIQUEIDENTIFIER    
        Declare @old_CreatedDate DATETIME    
        Declare @old_UpdatedBy UNIQUEIDENTIFIER    
        Declare @old_UpdatedDate DATETIME    
        Declare @old_IsDeleted BIT  


        -- declare temp fmId  
        Declare @fmId int  
        -- declare cursor  
        DECLARE curFormId cursor   
        FOR select Id from INSERTED   
        -- open cursor       
        OPEN curFormId  
        -- fetch row  
        FETCH NEXT FROM curFormId INTO @fmId  

        WHILE @@FETCH_STATUS  = 0   
        BEGIN   

        Select   
        @FormLineAttributeValueId = Id,   
        @old_Name = Name,  
        @old_Description = [Description],  

        @old_CreatedBy = CreatedBy,    
        @old_CreatedDate =CreatedDate,  
        @old_UpdatedBy =UpdatedBy,    
        @old_UpdatedDate =UpdatedDate,  
        @old_IsDeleted  = IsDeleted,  
        @currentxml = cast(RowHistory as NVARCHAR(MAX))  
        From DELETED where Id=@fmId  



        Select      
        @new_Name = Name,  
        @new_Description = [Description],  

        @new_CreatedBy = CreatedBy,    
        @new_CreatedDate =CreatedDate,  
        @new_UpdatedBy =UpdatedBy,    
        @new_UpdatedDate =UpdatedDate,  
        @new_IsDeleted  = IsDeleted  
        From INSERTED where Id=@fmId  

        set @old_Name = Replace(@old_Name,''&'',''&'')
        set @old_Name = Replace(@old_Name,''>'',''>'')  
        set @old_Name = Replace(@old_Name,''<'',''&lt;'')     
        set @old_Name = Replace(@old_Name,''"'',''&quot;'')
        set @old_Name = Replace(@old_Name,'''''''',''&apos;'')          

        set @new_Name = Replace(@new_Name,''&'',''&amp;'')      
        set @new_Name = Replace(@new_Name,''>'',''&gt;'')  
        set @new_Name = Replace(@new_Name,''<'',''&lt;'')     
        set @new_Name = Replace(@new_Name,''"'',''&quot;'')
        set @new_Name = Replace(@new_Name,'''''''',''&apos;'') 

        set @old_Description = Replace(@old_Description,''&'',''&amp;'')
        set @old_Description = Replace(@old_Description,''>'',''&gt;'')  
        set @old_Description = Replace(@old_Description,''<'',''&lt;'')     
        set @old_Description = Replace(@old_Description,''"'',''&quot;'')
        set @old_Description = Replace(@old_Description,'''''''',''&apos;'')          

        set @new_Description = Replace(@new_Description,''&'',''&amp;'')      
        set @new_Description = Replace(@new_Description,''>'',''&gt;'')  
        set @new_Description = Replace(@new_Description,''<'',''&lt;'')     
        set @new_Description = Replace(@new_Description,''"'',''&quot;'')
        set @new_Description = Replace(@new_Description,'''''''',''&apos;'')   

        set @xml = ''''     

        BEGIN      

        -- for Name  
        If ltrim(rtrim(IsNull(@new_Name,''''))) != ltrim(rtrim(IsNull(@old_Name,'''')))    
        set @xml = @xml + ''<ColumnInfo ColumnName="Name" OldValue="''+ @old_Name + ''" NewValue="'' + @new_Name + ''"/>''    

        -- for Description  
        If ltrim(rtrim(IsNull(@new_Description,''''))) != ltrim(rtrim(IsNull(@old_Description,'''')))    
        set @xml = @xml + ''<ColumnInfo ColumnName="Description" OldValue="''+ @old_Description + ''" NewValue="'' + @new_Description + ''"/>''    

        -- CreatedDate     
        If IsNull(@new_CreatedDate,'''') != IsNull(@old_CreatedDate,'''')  
        set @xml = @xml + ''<ColumnInfo ColumnName="CreatedDate" OldValue="''+ cast(isnull(@old_CreatedDate,'''') as varchar(100)) + ''" NewValue="'' + cast(isnull(@new_CreatedDate,'''') as varchar(100)) + ''"/>''    

        -- CreatedBy     
        If cast(IsNull(@new_CreatedBy,''00000000-0000-0000-0000-000000000000'')as varchar (36)) != cast(IsNull(@old_CreatedBy,''00000000-0000-0000-0000-000000000000'')as varchar(36))    
        set @xml = @xml + ''<ColumnInfo ColumnName="CreatedBy" OldValue="''+ cast(IsNull(@old_CreatedBy,''00000000-0000-0000-0000-000000000000'') as varchar(36)) + ''" NewValue="'' + cast(isnull(@new_CreatedBy,''00000000-0000-0000-0000-000000000000'') as varchar(36))+
        ''"/>''    

        -- UpdatedDate       
        If IsNull(@new_UpdatedDate,'''') != IsNull(@old_UpdatedDate,'''')    
        set @xml = @xml + ''<ColumnInfo ColumnName="UpdatedDate" OldValue="''+ cast(IsNull(@old_UpdatedDate,'''') as varchar(100)) + ''" NewValue="'' + cast(IsNull(@new_UpdatedDate,'''') as varchar(100)) + ''"/>''    

        -- UpdatedBy     
        If cast(IsNull(@new_UpdatedBy,''00000000-0000-0000-0000-000000000000'') as varchar(36)) != cast(IsNull(@old_UpdatedBy,''00000000-0000-0000-0000-000000000000'') as varchar(36))    
        set @xml = @xml + ''<ColumnInfo ColumnName="UpdatedBy" OldValue="''+ cast(IsNull(@old_UpdatedBy,''00000000-0000-0000-0000-000000000000'') as varchar(36)) + ''" NewValue="'' + cast(IsNull(@new_UpdatedBy,''00000000-0000-0000-0000-000000000000'') as varchar(36))+
        ''"/>''    

        -- IsDeleted  
        If cast(IsNull(@new_IsDeleted,'''') as varchar(10)) != cast(IsNull(@old_IsDeleted,'''') as varchar(10))    
        set @xml = @xml + ''<ColumnInfo ColumnName="IsDeleted" OldValue="''+ cast(IsNull(@old_IsDeleted,'''') as varchar(10)) + ''" NewValue="'' + cast(IsNull(@new_IsDeleted,'''') as varchar(10)) + ''" />''    

        END    

        Set @xml = ''<RowInfo TableName="te_Page" UpdatedBy="'' + cast(IsNull(@new_UpdatedBy,''00000000-0000-0000-0000-000000000000'') as varchar(50)) +  ''" UpdatedDate="'' + Convert(Varchar(20),GetDate()) + ''">'' + @xml + ''</RowInfo>''    
        Select @ishistoryexists = RowHistory From DELETED     

        --print @ishistoryexists  


        If @ishistoryexists is null    
        Begin     
        Set @xml = ''<History>'' + @xml + ''</History>''      
        Update te_Page    
        Set    
        RowHistory = @xml    
        Where     
        Id = @FormLineAttributeValueId    

        End    

        Else    
        Begin     
        set @xml = REPLACE(@currentxml, ''<History>'', ''<History>'' + @xml)  
        Update te_Page  
        Set  
        RowHistory = @xml  
        Where   
        Id = @FormLineAttributeValueId     
        End  


        FETCH NEXT FROM curFormId INTO @fmId  
        END   


        CLOSE curFormId  
        DEALLOCATE curFormId  

Ahora, cada vez que realice alguna actualización, sus datos se almacenarán en la columna rowhistory

Answer 8

Acerca del registro de usuarios que cambia DB: Puede crear tantos usuarios SQL como necesite para su DB y si usa sesiones y acceso restringido / registrado a su programa / script puede usar esa información para iniciar diferentes configuraciones de conexión DB (es decir, nombre de usuario) , antes de cualquier operación con DB.

Al menos eso debería ser factible para los scripts de PHP, pero podría estar equivocado para asp.net.