Записывать изменения в таблицу базы данных с помощью триггера
Я ищу хороший способ регистрировать изменения, которые происходят в определенном наборе таблиц в моей базе данных SQL Server 2005. Я считаю, что лучший способ сделать это через триггер, который выполняется при обновлении и удалении. Есть ли способ захватить фактическое заявление, которое выполняется? Как только у меня есть заявление, я могу легко записать его в другом месте (другая таблица БД). Однако я не нашел простой способ (если это возможно) получить оператор SQL, который выполняется.
11 ответов
Триггеры плохие, я бы держался подальше от триггеров.
Если вы пытаетесь устранить неполадки, присоедините Sql Profiler к базе данных с конкретными условиями. Это будет регистрировать каждый запрос, выполненный для вашей проверки.
Другой вариант - перейти к вызывающей программе, чтобы регистрировать ее запросы. Это очень распространенная практика.
Если вы просто хотите сохранить журнал всех транзакций (вставка, обновление и удаление) в некоторых таблицах базы данных, вы можете запустить следующий скрипт:
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'Audit')
CREATE TABLE LogTable
(
LogID [int]IDENTITY(1,1) NOT NULL,
Type char(1),
TableName varchar(128),
PrimaryKeyField varchar(1000),
PrimaryKeyValue varchar(1000),
FieldName varchar(128),
OldValue varchar(1000),
NewValue varchar(1000),
UpdateDate datetime DEFAULT (GetDate()),
UserName varchar(128)
)
GO
DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON
SELECT @TABLE_NAME= MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.Tables
WHERE
--query for table that you want to audit
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'LogTable'
AND TABLE_NAME!= 'one table to not record de log';
WHILE @TABLE_NAME IS NOT NULL
BEGIN
SELECT 'PROCESANDO ' + @TABLE_NAME;
EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')
SELECT @sql = 'create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete
as
declare
@bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)
select @TableName = ''' + @TABLE_NAME+ '''
-- date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)
-- Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = ''U''
else
select @Type = ''I''
else
select @Type = ''D''
-- get list of columns
select * into #ins from inserted
select * into #del from deleted
-- Get primary key columns for full outer join
select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key fields select for insert(comma deparated)
select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + '',''''''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key values for insert(comma deparated as varchar)
select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))'' + ''+'''',''''''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
if @PKCols is null
begin
raiserror(''no PK on table %s'', 16, -1, @TableName)
return
end
select @sql = ''insert LogTable(Type, TableName, PrimaryKeyField, PrimaryKeyValue, UserName)''
select @sql = @sql + '' select '''''' + @Type + ''''''''
select @sql = @sql + '','''''' + @TableName + ''''''''
select @sql = @sql + '','' + @PKFieldSelect
select @sql = @sql + '','' + @PKValueSelect
select @sql = @sql + '','''''' + @UserName + ''''''''
select @sql = @sql + '' from #ins i full outer join #del d''
select @sql = @sql + @PKCols
exec (@sql)
';
SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
--query for table that you want to audit
AND TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'LogTable'
AND TABLE_NAME!= 'one table to not record de log';
END
Вы должны быть в состоянии сделать это, используя представления управления системой.
Примером будет что-то вроде этого:
SELECT er.session_id,
er.status,
er.command,
DB_NAME(database_id) AS 'DatabaseName',
user_id,
st.text
FROM sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
WHERE er.session_id = @@SPID;
Я не уверен, что это будет так же полезно для вас, как механизм регистрации, ориентированный на данные.
Не забывайте, что ваше ведение журнала будет частью транзакции, поэтому в случае возникновения ошибки и отката транзакции ваш журнал также будет удален.
Существует шаблон для создания таких триггеров, который называется Log Trigger. Это не зависит от производителя и очень просто. Это описано здесь.
Изменения записываются в другую таблицу истории. Нет точного способа получить точное утверждение, но можно определить, была ли это вставка, и обновить или удалить, потому что это создает "связанный" набор записей. Вставка - это запись без предшественника, удаление - это запись без преемника, промежуточные записи - это обновления. Изменения могут быть обнаружены при сравнении записи с ее предшественником.
Очень легко получить снимок одного объекта (или всей таблицы) в данный момент времени.
В качестве бонуса, синтаксис этого шаблона для SQL Server оказывается самым простым по сравнению с Oracle, DB2 и MySQL.
В MSSQL есть виртуальные таблицы с именами "Вставлено" и "Удалено", которые содержат вновь вставленные и / или вновь удаленные и / или недавно обновленные записи данных, к которым вы можете обращаться из триггера... Я использую их, чтобы знать, что данные изменились (это вместо того, чтобы сказать, какой оператор изменил данные).
Используйте журнал Trigger
Существует небольшая причина для захвата фактического SQL, поскольку может быть много разных операторов, которые изменяют данные одинаково.
Действительно ли вам нужно регистрировать оператор, который выполнялся, большинство людей регистрируют измененные данные (таблицы INSERTED и DELETED внутри триггера).
Триггеры являются хорошим способом гарантировать, что любые изменения регистрируются, поскольку они почти всегда запускаются независимо от того, как выполняются обновления - например, специальные соединения, а также соединения приложений.
Как предлагает @mwigdahl, представления управления системой выглядят как хороший способ для захвата текущего запущенного пакета. Является ли это особенно полезным для входа в триггер, это другое дело.
Недостатком использования триггеров является то, что вы можете определить источник обновления только из соединения с базой данных. Многие приложения не имеют никакой пользовательской информации, связанной с соединением, чтобы упростить пул соединений, поэтому вы не знаете, какой пользователь выполняет действие. т. е. логин, используемый соединением, является общим логином приложения, а не лицом, использующим приложение. Обычный способ обойти это - использовать хранимые процедуры в качестве интерфейса для всех взаимодействий с базой данных, а затем убедиться, что UserId передается со всеми вызовами процедур. Затем вы можете выполнить регистрацию через хранимую процедуру вместо триггера. Очевидно, что это полезно только в том случае, если вы знаете, что люди не будут обновлять таблицы напрямую, не используя процедуры, или вам не нужно регистрировать эту ситуацию.
Возможность получить выполняемый в данный момент пакет может обеспечить еще лучший механизм: если вы убедитесь, что все ваши sql-пакеты содержат UserId, вы можете извлечь его из sql в вашем триггере. Это позволит вам вести всю регистрацию с помощью триггеров, что означает, что вы захватываете все, но также позволяет связывать изменения с конкретным пользователем.
Если вы идете по маршруту триггера, стоит проверить, не запускаются ли триггеры ситуаций (возможно, загруженные данные? Или если у людей есть разрешение на отключение триггеров).
Также обратите внимание на то, что @idstam указал, что код триггера будет находиться в вашей транзакции, поэтому обычно регистрируется и откатывается вместе с ним.
При написании триггеров следует учитывать и поведение @@IDENTITY: если у вас есть процедуры, использующие @@IDENTITY, вы можете случайно изменить их поведение.
Попробуйте установить какой-нибудь сторонний инструмент, основанный на триггерах, такой как ApexSQL Audit, а затем перепроверить, как они это делают. Просто установите его в пробном режиме и посмотрите, как он генерирует триггеры для сбора всех видов информации.
Несколько других вещей, чтобы рассмотреть:
Планирование хранилища - если у вас много обновлений, это означает, что у вас будет масса данных аудита. Я хотел бы рассмотреть эти данные в отдельных базах данных. Особенно, если вы планируете проверять более одной базы данных.
Управление объемом данных - со временем вам, вероятно, не понадобится вести очень старые записи. Планируйте легкое удаление старых данных
Изменения схемы - что, если схема обновлена. В худшем случае ваши триггеры перестают работать и выдают ошибку, если они не созданы правильно. В лучшем случае вы пропустите некоторые данные. Это тоже то, что нужно учитывать.
Принимая все это во внимание, вероятно, наиболее эффективно использовать какое-то уже разработанное решение, а не создавать его с нуля.
Это адаптировано из ответа Хуана Карлоса Велеса. Я изменил его, чтобы учесть составные первичные ключи и имена столбцов, содержащие пробелы. Кроме того, я прокомментировал это повсюду, чтобы тот, кто хочет изменить его для своих целей, мог понять, что происходит на каждом этапе, если код им непонятен.
-- This stops the message that shows the count of the number of rows affected from being returned as part of the result set.
set nocount on
-- If the Audit table doesn't exist, create it.
if not exists(select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Audit')
create table Audit
(
AuditID [int] identity(1,1) not null,
[Type] char(1),
TableName nvarchar(128),
PKFields nvarchar(max),
PKValues nvarchar(max),
FieldName nvarchar(128),
OldValue nvarchar(max),
NewValue nvarchar(max),
UpdateDate datetime,
UserName nvarchar(128)
)
go
-- Variables for the dynamic SQL and table name.
declare @tr nvarchar(max),
@tableName sysname
-- Get the first table in database. Skip over views and a few specified tables.
select @tableName = min(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME <> 'sysdiagrams' and TABLE_NAME <> 'Audit'
---- If you want to specify certain tables, uncomment the next line and add your table names.
--and (TABLE_NAME = 'IGAs' or TABLE_NAME = 'IGABudgets' or TABLE_NAME = 'Resolutions' or TABLE_NAME = 'RTAProjects' or TABLE_NAME = 'RTASubProjects')
-- Loop through the tables in the database and create an audit trigger on each one.
while @tableName is not null
begin
-- If a trigger of the same name already exists, delete it.
exec('if OBJECT_ID (''' + @tableName + '_ChangeTracking'', ''TR'') is not null drop trigger ' + @tableName + '_ChangeTracking')
-- Check if there is a primary key. If not, throw an error.
if (select count(*) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS c, INFORMATION_SCHEMA.KEY_COLUMN_USAGE u where c.TABLE_NAME = @tableName and c.CONSTRAINT_TYPE = 'PRIMARY KEY' and u.TABLE_NAME = c.TABLE_NAME and u.CONSTRAINT_NAME = c.CONSTRAINT_NAME) = 0
begin
raiserror('Error: There is no primary key on table %s', 16, -1, @tableName)
return
end
-- Create the trigger.
select @tr = 'create trigger ' + @tableName + '_ChangeTracking on ' + @tableName + ' for insert, update, delete as
-- Misc variables.
declare @table nvarchar(128),
@fieldName nvarchar(128) = '''',
@type char(1),
@pkJoin nvarchar(max),
@pkSelect nvarchar(max),
@pkFields nvarchar(max),
@pkValues nvarchar(max),
@updateDate nvarchar(30) = convert(varchar(30), getdate(), 22),
@user nvarchar(128) = system_user,
@sql nvarchar(max),
@params nvarchar(max) = N''@out nvarchar(max) output'',
@fieldIndex int = 0,
@maxField int,
@bit int,
@char int
-- Get the table name.
select @table = object_name(parent_id) from sys.triggers where object_id = @@PROCID
-- Get the modification type: U = update, I = insert, D = delete
if exists (select * from inserted)
if exists (select * from deleted)
select @type = ''U''
else select @type = ''I''
else select @type = ''D''
-- Save the inserted and deleted values into temp tables.
select * into #ins from inserted
select * into #del from deleted
-- Get the number of columns in the table.
select @maxField = max(columnproperty(object_id(@table), COLUMN_NAME, ''ColumnID'')) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @table
-- Get the primary key join relationship(s).
select @pkJoin = coalesce(@pkJoin + '' and'', '' on'') + '' i.['' + u.COLUMN_NAME + ''] = d.['' + u.COLUMN_NAME + '']''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS c, INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
where c.TABLE_NAME = @table
and c.CONSTRAINT_TYPE = ''PRIMARY KEY''
and u.TABLE_NAME = c.TABLE_NAME
and u.CONSTRAINT_NAME = c.CONSTRAINT_NAME
-- Get the primary key field name(s).
select @pkFields = coalesce(@pkFields + '', '', '''') + ''['' + u.COLUMN_NAME + '']''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS c, INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
where c.TABLE_NAME = @table
and c.CONSTRAINT_TYPE = ''PRIMARY KEY''
and u.TABLE_NAME = c.TABLE_NAME
and u.CONSTRAINT_NAME = c.CONSTRAINT_NAME
-- Get the primary key field(s) for select statement.
select @pkSelect = coalesce(@pkSelect + '' + '''', '''' + '', '''') + ''convert(nvarchar(max), ['' + u.COLUMN_NAME + ''])''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS c, INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
where c.TABLE_NAME = @table
and c.CONSTRAINT_TYPE = ''PRIMARY KEY''
and u.TABLE_NAME = c.TABLE_NAME
and u.CONSTRAINT_NAME = c.CONSTRAINT_NAME
-- Get the primary key field value(s).
if (@type = ''D'')
begin
set @sql = ''select @out = '' + @pkSelect + '' from #del''
exec sp_executesql @sql, @params, @out = @pkValues output
end
else
begin
set @sql = ''select @out = '' + @pkSelect + '' from #ins''
exec sp_executesql @sql, @params, @out = @pkValues output
end
-- Loop through each field in the inserted table.
while @fieldIndex < @maxField
begin
-- Iterate the fieldIndex.
select @fieldIndex = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @table and columnproperty(object_id(@table), COLUMN_NAME, ''ColumnID'') > @fieldIndex
-- If the column in scope has been modified, insert a record into the Audit table.
select @bit = (@fieldIndex - 1)% 8 + 1
select @bit = POWER(2, @bit - 1)
select @char = ((@fieldIndex - 1) / 8) + 1
if substring(columns_updated(), @char, 1) & @bit > 0 or @Type IN (''I'', ''D'')
begin
-- Get the name of the field whose ColumnID equals the current fieldIndex.
select @fieldName = ''['' + COLUMN_NAME + '']'' from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @table and columnproperty(object_id(@table), COLUMN_NAME, ''ColumnID'') = @fieldIndex '
-- Select statements have a length limitation. End the statement, then add the rest.
select @tr = @tr + '
set @sql = ''insert into Audit (Type, TableName, PKFields, PKValues, FieldName, OldValue, NewValue, UpdateDate, UserName) select '''''' + @type + '''''', '''''' + @table + '''''', '''''' + @pkFields + '''''', '''''' + @pkValues + '''''', '''''' + @fieldName + '''''', convert(nvarchar(max), d.'' + @fieldName + ''), convert(nvarchar(max), i.'' + @fieldName + ''), '''''' + @updateDate + '''''', '''''' + @user + '''''' from #ins i full outer join #del d'' + @pkJoin + '' where i.'' + @fieldName + '' <> d.'' + @fieldName + '' or (i.'' + @fieldName + '' is null and d.'' + @fieldName + '' is not null) or (i.'' + @fieldName + '' is not null and d.'' + @fieldName + '' is null)''
--print(@sql)
exec(@sql)
end
end'
---- This is if you want to see the statement that is generated rather than execute it.
--select @tr
-- Execute the trigger statement.
exec(@tr)
-- Iterate the table name.
select @tableName = min(TABLE_NAME) from INFORMATION_SCHEMA.TABLES
where TABLE_NAME > @tableName and
TABLE_TYPE = 'BASE TABLE' and TABLE_NAME <> 'sysdiagrams' and TABLE_NAME <> 'Audit'
---- If you want to specify certain tables, uncomment the next line and add your table names.
--and (TABLE_NAME = 'IGAs' or TABLE_NAME = 'IGABudgets' or TABLE_NAME = 'Resolutions' or TABLE_NAME = 'RTAProjects' or TABLE_NAME = 'RTASubProjects')
end
Будьте осторожны, поскольку триггеры срабатывают на уровне ROW, а не на уровне SQL STATEMENT. Таким образом, если кто-то выполнит команду "DELETE FROM BIGTABLE", ваш триггер сработает для каждой строки в этой таблице (в частности, в связи с тем, что вы хотите знать оператор SQL, который выполнил операцию, поэтому вам нужно "вычислить" это "для каждой строки, на которую влияет утверждение).