Как получить все журналы транзакций (вставить обновление удалить) для конкретной таблицы в SQL Server 2008
Я хочу получить все транзакции, примененные к определенной таблице в SQL Server 2008.
Я нашел последний раз, когда таблица была обновлена с помощью этого сценария:
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'DBName')
AND OBJECT_ID=OBJECT_ID('tableName')
Я хочу знать все транзакции (вставки, обновления, удаления) для этой таблицы, их дату и время, а также примененный запрос.
Каков наилучший способ сделать это?
4 ответа
Создание триггера, который создаст новую таблицу Emp_audit и добавит в нее новые кортежи всякий раз, когда в таблицу вносится какое-либо изменение
create trigger my_trigger on Employees
AFTER INSERT, UPDATE, DELETE
AS
DECLARE @What varchar(30);
DECLARE @Who varchar(30);
DECLARE @for int;
DECLARE @At time;
DECLARE @COUNTI int;
DECLARE @COUNTD int;
select @COUNTI = COUNT(*) from inserted;
select @COUNTD = COUNT(*) from deleted;
set @Who = SYSTEM_USER;
set @At = CURRENT_TIMESTAMP;
if( @COUNTD = 0 and @COUNTI = 1)
begin
set @What = 'insert';
select @for = EmployeeID from inserted i;
end
else
begin
if( @COUNTD = 1 and @COUNTI = 0)
begin
set @What = 'delete';
select @for = EmployeeID from deleted i;
end
else
begin
set @What = 'update';
select @for = EmployeeID from inserted i;
end
end
INSERT INTO EMP_Audit Values (@What, @Who, @for, @At);
Единственный способ сделать это за разумное время - использовать сторонний инструмент (как Мартин сказал в первом комментарии), такой как ApexSQL Log, который может читать журнал транзакций и получать необходимую информацию.
Обратите внимание, что для того, чтобы это работало, ваша база данных должна находиться в режиме полного восстановления, потому что именно тогда SQL Server регистрирует полные детали транзакций, которые можно восстановить позже.
Другой вариант - изучить, как использовать недокументированную функцию fn_dblog, но это займет у вас намного больше времени, и вы не сможете читать отдельные журналы или резервные копии журналов транзакций.
Было бы гораздо лучше настроить аудит для этой необходимости, чем пытаться извлечь эту информацию ретроспективно из журнала транзакций.
Если вы используете Enterprise Edition, вы можете использовать встроенную функцию аудита SQL Server, в противном случае будет достаточно просто записать нужную информацию с помощью триггеров.
Вы можете создавать свои собственные журналы транзакций
Шаг 1. Создайте собственную таблицу для журналов транзакций
CREATE TABLE [dbo].[TransactionLogs](
[TransactionLogID] [bigint] IDENTITY(1,1) NOT NULL,
[Query] [nvarchar](max) NOT NULL,
[DateCreated] [datetime] NOT NULL,
CONSTRAINT [PK_TransactionLogs] PRIMARY KEY CLUSTERED
(
[TransactionLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Шаг 2: Создайте хранимую процедуру, которая создает журналы. (Примечание: замените YourTablePKColumn столбцом первичного ключа таблицы.)
create procedure [dbo].[sp_CreateQueryLogs]
(
@Query nvarchar(max) = null output,
@TableName nvarchar(100),
@YourTablePKColumn nvarchar(30),
@QueryTypeID tinyint --0 insert, 1 update, 2 delete
)
as
begin
declare @object_id bigint, @column_name nvarchar(100), @collation_name nvarchar(50), @column_name_id nvarchar(100) = null, @column_names nvarchar(max) = '', @column_values nvarchar(max) = '', @column_names_create nvarchar(max) = '', @values nvarchar(max) = '', @user_type_id int, @max_length nvarchar(10), @type_name nvarchar(50), @CreateTempTable nvarchar(max) = '', @is_nullable bit, @value nvarchar(max) = ''
create table #tmpValues(ColumnValues nvarchar(max))
insert into #tmpValues(ColumnValues)
exec('select CAST ( ( select * from ' + @TableName + ' where YourTablePKColumn = ' + @YourTablePKColumn + '
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) )')
select @values = ColumnValues from #tmpValues
if @QueryTypeID = 0 --insert
set @Query = 'insert into ' + @TableName + '('
else if @QueryTypeID = 1 --update
set @Query = 'update ' + @TableName + ' set '
else if @QueryTypeID = 2 --dalete
set @Query = 'delete ' + @TableName + ' '
select @object_id = object_id from sys.tables where name = @TableName
if not cursor_status('local','columnCursor') <= -1
begin
close columnCursor;
deallocate columnCursor;
end
declare columnCursor cursor local for
select name, user_type_id, convert(nvarchar(10), max_length), is_nullable from sys.columns where object_id = @object_id order by column_id ;
open columnCursor;
fetch next from columnCursor
into @column_name, @user_type_id, @max_length, @is_nullable;
while @@FETCH_STATUS = 0
begin
select @type_name = name, @collation_name = collation_name from sys.types where user_type_id = @user_type_id
if @column_name_id is null
set @column_name_id = @column_name
else
begin
set @column_names += @column_name + ', '
declare @value_keys_start nvarchar(max) = '<' + @column_name + '>', @value_keys_end nvarchar(max) = '</' + @column_name + '>'
if charindex(@value_keys_start,@values,1) = 0
begin
if @QueryTypeID = 0 --insert
set @column_values += 'null,'
else if @QueryTypeID = 1 --update
set @column_values += @column_name + ' = null,'
end
else
begin
if @QueryTypeID = 0 --insert
if @collation_name is null and not (@type_name like '%date%' or @type_name like '%time%')
set @column_values += substring(@values, charindex(@value_keys_start,@values,1) + len(@value_keys_start), charindex(@value_keys_end,@values,1) - (charindex(@value_keys_start,@values,1) + len(@value_keys_start))) + ','
else if @type_name like '%date%' or @type_name like '%time%'
set @column_values += '''' + replace(substring(@values, charindex(@value_keys_start,@values,1) + len(@value_keys_start), charindex(@value_keys_end,@values,1) - (charindex(@value_keys_start,@values,1) + len(@value_keys_start))),'T',' ') + ''','
else
set @column_values += '''' + replace(substring(@values, charindex(@value_keys_start,@values,1) + len(@value_keys_start), charindex(@value_keys_end,@values,1) - (charindex(@value_keys_start,@values,1) + len(@value_keys_start))),'''','''''') + ''','
else if @QueryTypeID = 1 --update
if @collation_name is null and not (@type_name like '%date%' or @type_name like '%time%')
set @column_values += @column_name + '=' + substring(@values, charindex(@value_keys_start,@values,1) + len(@value_keys_start), charindex(@value_keys_end,@values,1) - (charindex(@value_keys_start,@values,1) + len(@value_keys_start))) + ','
else if @type_name like '%date%' or @type_name like '%time%'
set @column_values += @column_name + '=' + '''' + replace(substring(@values, charindex(@value_keys_start,@values,1) + len(@value_keys_start), charindex(@value_keys_end,@values,1) - (charindex(@value_keys_start,@values,1) + len(@value_keys_start))),'T',' ') + ''','
else
set @column_values += @column_name + '=' + '''' + replace(substring(@values, charindex(@value_keys_start,@values,1) + len(@value_keys_start), charindex(@value_keys_end,@values,1) - (charindex(@value_keys_start,@values,1) + len(@value_keys_start))),'''','''''') + ''','
end
end
fetch next from columnCursor
into @column_name, @user_type_id, @max_length, @is_nullable;
end
if not cursor_status('local','columnCursor') <= -1
begin
close columnCursor;
deallocate columnCursor;
end
if @QueryTypeID = 0 --insert
set @Query += substring(@column_names,1,len(@column_names) - 1) + ')
values (' + substring(@column_values,1,len(@column_values) - 1) + ')'
else if @QueryTypeID = 1 --update or delete
set @Query += substring(@column_values,1,len(@column_values) - 1) + ' where YourTablePKColumn = ' + @YourTablePKColumn
else
set @Query += ' where YourTablePKColumn = ' + @YourTablePKColumn
end
Шаг 3. Создан триггер для таблицы, для которой вы хотите иметь журналы транзакций.
CREATE TRIGGER trg_MyTrigger ON YouTableName
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
SET NOCOUNT ON;
declare @TableName nvarchar(100) = 'YouTableName', @Query nvarchar(max), @QueryTypeID tinyint, @YourTablePKColumn nvarchar(30)
if exists(select * from deleted) and exists(select * from inserted)
begin
set @QueryTypeID = 1
if not cursor_status('local','updatedCursor') <= -1
begin
close updatedCursor;
deallocate updatedCursor;
end
declare updatedCursor cursor local for
select cast(YourTablePKColumn as nvarchar(30)) from inserted;
open updatedCursor;
fetch next from updatedCursor
into @YourTablePKColumn;
while @@FETCH_STATUS = 0
begin
exec dbo.sp_CreateQueryLogs @Query = @Query output, @TableName = @TableName, @YourTablePKColumn = @YourTablePKColumn, @QueryTypeID = @QueryTypeID
insert into TransactionLogs
(Query, DateCreated)
values (@Query,getdate())
fetch next from updatedCursor
into @YourTablePKColumn;
end
if not cursor_status('local','updatedCursor') <= -1
begin
close updatedCursor;
deallocate updatedCursor;
end
end
else if exists(select * from deleted) and not exists(select * from inserted)
begin
set @QueryTypeID = 2
if not cursor_status('local','deletedCursor') <= -1
begin
close deletedCursor;
deallocate deletedCursor;
end
declare deletedCursor cursor local for
select cast(YourTablePKColumn as nvarchar(30)) from deleted;
open deletedCursor;
fetch next from deletedCursor
into @YourTablePKColumn;
while @@FETCH_STATUS = 0
begin
exec dbo.sp_CreateQueryLogs @Query = @Query output, @TableName = @TableName, @YourTablePKColumn = @YourTablePKColumn, @QueryTypeID = @QueryTypeID
insert into TransactionLogs
(Query, DateCreated)
values (@Query,getdate())
fetch next from deletedCursor
into @YourTablePKColumn;
end
if not cursor_status('local','deletedCursor') <= -1
begin
close deletedCursor;
deallocate deletedCursor;
end
end
else
begin
set @QueryTypeID = 0
if not cursor_status('local','insertedCursor') <= -1
begin
close insertedCursor;
deallocate insertedCursor;
end
declare insertedCursor cursor local for
select cast(YourTablePKColumn as nvarchar(30)) from inserted;
open insertedCursor;
fetch next from insertedCursor
into @YourTablePKColumn;
while @@FETCH_STATUS = 0
begin
exec dbo.sp_CreateQueryLogs @Query = @Query output, @TableName = @TableName, @YourTablePKColumn = @YourTablePKColumn, @QueryTypeID = @QueryTypeID
insert into TransactionLogs
(Query, DateCreated)
values (@Query,getdate())
fetch next from insertedCursor
into @YourTablePKColumn;
end
if not cursor_status('local','insertedCursor') <= -1
begin
close insertedCursor;
deallocate insertedCursor;
end
end
END
GO