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

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

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

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

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

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

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

Но вот где это получилось немного странно ...

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

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

Хотя есть много способов снять шкуру с кошки ...

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

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

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

таблица добавления / обновления журнала выглядит так дата и время table_name, имя_столбца, record_id, old_value, новое_значение, ID пользователя, компьютер

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

Во-первых, во всех ваших таблицах вы должны добавить хотя бы эти столбцы в столбцы данных DateCreated, UserCreated, DateModified, UserModified. Возможно, вы захотите добавить столбец «Статус» или «LastAction», чтобы вы никогда не удаляли строку, вы просто устанавливаете для нее статус удален / вставлен / обновлен. Затем вы можете создать «Историческую таблицу», которая является точной копией первой таблицы. Затем при любых обновлениях или удалениях триггер должен копировать записи таблицы Deleted в таблицу History, одновременно изменяя поля DateModified, UserModified и Status.

У меня была установка в SQL Server, где мы использовали бы представления для доступа к нашим данным, которые обрабатывали бы вставки, обновления и удаления с триггерами INSTEAD OF.

Например: триггер ВМЕСТО УДАЛЕНИЯ в представлении будет отмечать записи в базовой таблице как удаленные, а представление было отфильтровано, чтобы не отображать удаленные записи.

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

Чтобы это работало, представление должно быть привязано к схеме.

О регистрации пользователей, которые изменяют БД: вы можете создать столько пользователей SQL, сколько вам нужно для своей БД, и если вы используете сеансы и ограниченный / зарегистрированный доступ к вашей программе / скрипту вы можете использовать эту информацию, чтобы инициировать различные настройки подключения к БД (например, имя пользователя), перед любой работой с БД.

По крайней мере, это должно быть выполнено для сценариев PHP, но я могу ошибаться для asp.net.

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

    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,'&','&')
        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  

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