Отслеживание изменений в базе данных SQL Server 2005

Мне было поручено разработать решение, отслеживающее изменения в базе данных.

Для обновлений мне нужно сделать следующее:

  • дата обновления
  • старое значение
  • новое значение
  • поле
  • человек, делающий изменения
  • идентификатор записи
  • запись таблицы находится в

Для удаления:

  • дата удаления
  • человек, делающий удаление
  • Заголовок / описание / id записи удалены. В таблицах, которые я отслеживаю изменения, у всех есть поле названия или описания. Я хотел бы зафиксировать это до того, как запись будет удалена.
  • таблица была в

Для вставок:

  • дата вставки
  • человек, делающий изменения
  • идентификатор записи
  • запись таблицы находится в

Я подумал о нескольких способах сделать это:

  • Я использую хранимые процедуры для любых обновлений / удалений / вставок. Я бы создал общую таблицу отслеживания. У него было бы достаточно полей для захвата всех данных. Затем я добавлю еще одну строку в каждом сохраненном proc в действие «Вставить запись в таблицу отслеживания».
    • недостаток: все обновления / удаления / вставки все перемешаны в одной таблице
    • много полей NULLed
    • как отслеживать пакетные обновления / удаляет / вставляет? <---- это не может быть проблемой. Я действительно не делаю ничего подобного в приложении.
    • как я могу захватить пользователя, делающего обновление. В базе данных только одна учетная запись.
    • отредактируйте много существующего кода для редактирования.
  • Наконец, я могу создать триггер, который вызывается после обновления / удаления / вставки. Многие из тех же недостатков, что и первое решение, за исключением: мне пришлось бы редактировать столько кода. Я не уверен, как я буду отслеживать обновления. Не похоже, чтобы триггеры могли видеть недавно обновленные записи.

Я использую asp.net, C #, sql server 2005, iis6, windows 2003. У меня нет бюджета, поэтому, к сожалению, я не могу ничего купить, чтобы помочь мне в этом.

Спасибо за ваши ответы!

sql-server,tracking,change-management,

9

Ответов: 8


4 принят

Триггер не будет иметь всю необходимую информацию по целому ряду причин, но идентификатор пользователя не является ключом.

Я бы сказал, что вы на правильном пути с общим sp, чтобы вставлять туда, где сделаны изменения. Если вы стандартизируете на sp для своих интерфейсов, то вы опережаете игру - сложно будет прокрасться в изменение, которое не отслеживается.

Посмотрите на это как эквивалент аудиторского следа в приложении учета - это журнал - одна таблица с каждой записанной транзакцией. Они не будут использовать отдельные журналы для депозитов, снятий, корректировок и т. Д., И это тот же принцип.


4

Мне не нравится переходить к проблеме, и я знаю, что у вас нет бюджета, но самым простым решением будет обновление до SQL Server 2008. У этой функции встроена эта функция . Я думал, что, по крайней мере, следует упомянуть кого-нибудь, кто сталкивается с этим вопросом, даже если вы не можете использовать его самостоятельно.

(Среди развертываемых выпусков SQL 2008 эта функция доступна только в Enterprise.)


3

Я предлагаю вам использовать 2 колонки в каждой таблице. имена rowhistory и IsDeleted, а тип данных будет xml и бит. Никогда не удаляйте строки, всегда используйте флаг IsDeleted. Теперь идите с триггерами обновлений. Я приведу вам пример для того же самого, у меня есть эта таблица под названием «Страница

    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]

Теперь после создания таблицы все, что вам нужно сделать, это скопировать вставьте код ниже, и ваша задача выполняется для таблицы «Таблица». Он начнет записывать историю строки в той же строке, которая обновляется вместе со старыми и новыми значениями.

                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,'&','&amp;')
        set @old_Name = Replace(@old_Name,'>','&gt;')  
        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  

Теперь, когда вы будете выполнять какое-либо обновление, ваши данные будут сохранены в столбце rowhistory


1

Один из способов, с помощью которого я видел, что это обрабатывается (хотя я бы не рекомендовал, честно говоря), обрабатывать его с помощью хранимых процедур, передавая в userid / username / whatever как параметр. Хранимые процедуры вызовут процедуру ведения журнала, в которой соответствующие данные будут записаны в центральной таблице журналов.

Вот где это получилось немного странно, хотя ...

Для INSERT / UPDATE соответствующие строки (строки) были сохранены в таблице в виде XML-данных после успешного завершения INSERT / UPDATE. Для DELETEs строка была сохранена до запуска DELETE (хотя, по существу, они могли получить ее из вывода DELETE - по крайней мере, с SQL Server 2005).

Если я правильно помню, таблица имела только пару столбцов: UserID, DateTime журнала, тип транзакции (I / U / D), данные XML, содержащие соответствующие строки, имя таблицы и значение первичного ключа (в основном используется для быстрого поиска из каких записей они хотели).

Многие способы кошки кошки, хотя ...

Мой совет - сохранить просто. Разверните его позже, если / когда вам нужно.

Если у вас есть возможность сделать это, заблокируйте пользователей, чтобы они могли выполнять действительные операторы на таблицах через хранимые процедуры, а затем обрабатывать ведение журнала (однако, вы хотите) оттуда.


0

мы создали свои собственные и просто нуждались в том, чтобы пользователь и компьютер переходили в каждую хранимую процедуру добавления / обновления. то это просто вопрос получения оригинальной записи и заполнения переменных и сравнения их с переданными в переменных и протоколированием данных в нашу таблицу. для удаления мы просто имеем копию исходных таблиц + поле временной метки, поэтому запись никогда не удаляется и может быть восстановлена ??в любое время (очевидно, что процедура удаления проверяет отношения FK и т. д.).

таблица добавления / обновления журнала выглядит как datetime, table_name, column_name, record_id, old_value, new_value, user_id, компьютер

мы никогда не вставляем нули, поэтому мы конвертируем их в пустые строки, новые записи помечены «{new entry}» в столбце old_value. record_id состоит из множества ключевых столбцов, чтобы однозначно идентифицировать эту единственную запись (поле1 + '.' + field2 + ...)

SQL-сервер, отслеживания, управления изменениями,
Похожие вопросы