Отслеживание изменений в базе данных SQL Server 2005
Мне было поручено разработать решение, которое отслеживает изменения в базе данных.
Для обновления мне нужно захватить:
- дата обновления
- старое значение
- новое значение
- поле затронуто
- человек, делающий изменения
- идентификатор записи
- запись таблицы находится в
Для удаления:
- дата удаления
- человек, делающий удаление
- Заголовок / описание / идентификатор записи удален. Во всех таблицах, в которых я отслеживаю изменения, есть поле заголовка или описания. Я хотел бы захватить это, прежде чем запись будет удалена.
- запись таблицы была в
Для вставок:
- дата вставки
- человек, делающий изменения
- идентификатор записи
- запись таблицы находится в
Я подумал о нескольких способах сделать это:
- Я использую хранимые процедуры для любых обновлений / удалений / вставок. Я хотел бы создать общую таблицу "отслеживания". Было бы достаточно полей, чтобы захватить все данные. Затем я добавил бы еще одну строку в каждом сохраненном процессе для эффекта "Вставить запись в таблицу отслеживания".
- недостаток: все обновления / удаления / вставки перемешаны в одной таблице
- много пустых полей
- как отслеживать обновления / удаление / вставку пакетов? <---- это не может быть проблемой. Я действительно не делаю ничего подобного в приложении.
- как мне захватить пользователя, делающего обновление. База данных видит только одну учетную запись.
- редактировать много существующего кода для редактирования.
- Наконец, я мог бы создать триггер, который вызывается после обновления / удаления / вставки. Многие из тех же недостатков, что и первое решение, за исключением: мне придется редактировать столько же кода. Я не уверен, как я буду отслеживать обновления. Не похоже, что есть способ использовать триггеры для просмотра недавно обновленных записей.
Я использую asp.net, C#, SQL Server 2005, IIS6, Windows 2003. У меня нет бюджета, так что, к сожалению, я не могу купить что-нибудь, чтобы помочь мне с этим.
Спасибо за ваши ответы!
8 ответов
Триггер не будет иметь всей необходимой вам информации по ряду причин, но ни один идентификатор пользователя не является решающим фактором.
Я бы сказал, что вы на правильном пути с общим sp для вставки, где бы ни вносились изменения. Если вы стандартизируете sp для своих интерфейсов, значит, вы впереди игры - вам будет трудно скрыться в изменениях, которые не отслеживаются.
Рассматривайте это как эквивалент контрольного журнала в бухгалтерском приложении - это Журнал - отдельную таблицу с каждой записанной транзакцией. Они не будут внедрять отдельные журналы для депозитов, снятий, корректировок и т. Д., И это тот же принцип.
Я не хочу обойти проблему и знаю, что у вас нет бюджета, но самое простое решение - это обновление до SQL Server 2008. В него встроена эта функция. Я подумал, что по крайней мере следует упомянуть любого, кто сталкивается с этим вопросом, даже если вы не можете использовать его самостоятельно.
(Среди развертываемых выпусков SQL 2008 эта функция доступна только в Enterprise.)
Я бы посоветовал вам использовать 2 столбца в каждой таблице. имена rowhistory и 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,'<','<')
set @old_Name = Replace(@old_Name,'"','"')
set @old_Name = Replace(@old_Name,'''',''')
set @new_Name = Replace(@new_Name,'&','&')
set @new_Name = Replace(@new_Name,'>','>')
set @new_Name = Replace(@new_Name,'<','<')
set @new_Name = Replace(@new_Name,'"','"')
set @new_Name = Replace(@new_Name,'''',''')
set @old_Description = Replace(@old_Description,'&','&')
set @old_Description = Replace(@old_Description,'>','>')
set @old_Description = Replace(@old_Description,'<','<')
set @old_Description = Replace(@old_Description,'"','"')
set @old_Description = Replace(@old_Description,'''',''')
set @new_Description = Replace(@new_Description,'&','&')
set @new_Description = Replace(@new_Description,'>','>')
set @new_Description = Replace(@new_Description,'<','<')
set @new_Description = Replace(@new_Description,'"','"')
set @new_Description = Replace(@new_Description,'''',''')
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
Теперь, когда вы будете выполнять какие-либо обновления, ваши данные будут храниться в столбце истории
Один из способов, которым я видел эту обработку (хотя я бы не рекомендовал ее, если честно), - это обрабатывать ее с помощью хранимых процедур, передавая в качестве параметра идентификатор пользователя / имя пользователя / что угодно. Хранимые процедуры будут вызывать процедуру регистрации, которая записывает соответствующие подробности в центральную таблицу журнала.
Вот где это стало немного странно, хотя...
Для INSERT/UPDATE соответствующие строки были сохранены в таблице как данные XML после успешного завершения INSERT/UPDATE. Для DELETE строка была сохранена до запуска DELETE (хотя, реально, они могли бы получить ее из вывода оператора DELETE - по крайней мере, с SQL Server 2005).
Если я правильно помню, таблица имела только пару столбцов: UserID, DateTime для ведения журнала, тип транзакции (I/U/D), данные XML, содержащие соответствующие строки, имя таблицы и значение первичного ключа (в основном используется для быстрого поиска из каких записей они хотели).
Много способов снять кожу с кошки, хотя...
Мой совет, чтобы сохранить это просто. Расширьте это позже, если / когда вам нужно.
Если у вас есть возможность сделать это, заблокируйте пользователей, чтобы они могли выполнять действенные операторы только для таблиц с помощью хранимых процедур, а затем обрабатывайте ведение журнала (как хотите) оттуда.
Во-первых, во всех ваших таблицах должны быть по крайней мере эти столбцы, добавленные к столбцам данных DateCreated, UserCreated, DateModified, UserModified. Возможно, вам захочется добавить столбец "Состояние" или "LastAction", чтобы вы никогда не удаляли строку, а просто устанавливали ее как удаленный / вставленный / обновленный статус. Затем вы можете создать "Историческую таблицу", которая является точной копией первой таблицы. Затем при любом обновлении или удалении триггер должен скопировать записи таблицы "Удаленные" в таблицу "Журнал", одновременно изменяя поля "DateModified", "UserModified" и "Status".
У меня была установка в SQL Server, где мы будем использовать представления для доступа к нашим данным, которые будут обрабатывать вставки, обновления и удаления с помощью триггеров INSTEAD OF.
Например: триггер INSTEAD OF DELETE в представлении пометит записи в базовой таблице как удаленные, а представление будет отфильтровано, чтобы не показывать удаленные записи.
Во всех триггерах мы обновили дату модификации и имя пользователя. Проблема заключается в том, что в журнале регистрируется имя пользователя базы данных, которое не совпадает с конечным именем пользователя приложения.
Чтобы это работало, представление должно быть привязано к схеме.
О регистрации пользователей, которые изменяют БД: вы можете создать столько пользователей SQL, сколько вам нужно для вашей БД, и если вы используете сеансы и ограниченный / зарегистрированный доступ к вашей программе / сценарию, вы можете использовать эту информацию для инициирования различных настроек подключения к БД (т. Е. Имени пользователя) перед любой операцией с БД.
По крайней мере, это должно быть выполнимо для PHP-сценариев, но я могу ошибаться для asp.net.
Мы создали свой собственный и нам просто потребовалось, чтобы пользователь и компьютер передавались в каждую хранимую процедуру добавления / обновления. тогда нужно просто получить исходную запись и заполнить переменные, сравнить их с переданными переменными и записать данные в нашу таблицу. для удалений у нас просто есть копия исходных таблиц + поле отметки времени, поэтому запись никогда не удаляется и может быть восстановлена в любое время, когда нам нужно (очевидно, процедура удаления проверяет отношения FK и т. д.).
Таблица добавления / обновления журналов выглядит как datetime, table_name, column_name, record_id, old_value, new_value, user_id, компьютер
мы никогда не вставляем нули, поэтому мы конвертируем их в пустые строки, новые записи помечаются знаком '{новая запись}' в столбце old_value. record_id состоит из как можно большего количества ключевых столбцов, чтобы однозначно идентифицировать эту единственную запись ( field1 + '.' + field2 + ...)