使用触发器记录对数据库表的更改

I am looking for a good way to log changes that occur on a particular set of tables in my SQL Server 2005 database. I believe the best way to do this is through a trigger that get's executed on updates and deletes. Is there anyway to grab the actual statement that is being ran? Once I have the statement I can easily log it someplace else (other DB table). However, I have not found an easy way (if possible) to grab the SQL statement that is being ran.

我正在寻找一种很好的方法来记录在我的SQL Server 2005数据库中的特定表集上发生的更改。我认为最好的方法是通过一个触发器来执行更新和删除操作。反正有没有抓住正在运行的实际声明？一旦我有了语句，我就可以轻松地将其记录到其他地方（其他数据库表）。但是，我还没有找到一种简单的方法（如果可能的话）来获取正在运行的SQL语句。

11 个解决方案

#1

Triggers are bad, I'd stay away from triggers.

触发器很糟糕，我会远离触发器。

If you are trying to troubleshoot something, attach Sql Profiler to the database with specific conditions. This will log every query run for your inspection.

如果您尝试对某些内容进行故障排除，请将Sql Profiler附加到具有特定条件的数据库。这将记录每次查询运行以供检查。

Another option is to change to calling program to log its queries. This is a very common practice.

另一种选择是更改为调用程序以记录其查询。这是一种非常常见的做法。

#2

If you just want to keep a log of all transactions (insert, update and delete) in some database tables, then you can run the following script:

如果您只想在某些数据库表中保留所有事务（插入，更新和删除）的日志，则可以运行以下脚本：

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

#3

You should be able to accomplish this using the system management views.

您应该能够使用系统管理视图完成此操作。

An example would be something like this:

一个例子是这样的：

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;

I'm not sure this will be as useful to you as a more data-centric logging mechanism might be, though.

我不确定这对你有用，因为更多以数据为中心的日志记录机制可能会。

#4

Don't forget that your logging will be part of the transaction so if there is an error and you rollback the transaction, your log will also be deleted.

不要忘记您的日志记录将成为事务的一部分，因此如果出现错误并回滚事务，您的日志也将被删除。

#5

MSSQL has virtual tables named 'Inserted' and 'Deleted', which contain newly-inserted and/or newly-deleted and/or newly-updated data records, which you can access from a trigger ... I use these, to know what data has changed (that's instead of being told what statement changed the data).

MSSQL具有名为“已插入”和“已删除”的虚拟表，其中包含新插入和/或新删除和/或新更新的数据记录，您可以从触发器访问...我使用这些，知道什么数据已经改变（而不是被告知什么语句改变了数据）。

#6

There is a pattern for creating such these triggers called Log Trigger. This is vendor independent and very simple. It is described in here.

有一种模式可用于创建称为日志触发器的此类触发器。这是独立于供应商的，非常简单。它在这里描述。

The changes are recorded on another history table. There is no way to grab the exact statement, but it is possible to detect if it was an insertion, and update or a deletion because it creates a "chained" set of records. An insertion is a record with no predecessor, a deletion is a record with no successor, intermediate records are updates. Changes can be detected comparing a record against its predecessor.

更改记录在另一个历史记录表中。没有办法获取确切的语句，但可以检测它是否是插入，更新或删除，因为它创建了一个“链式”记录集。插入是没有前任的记录，删除是没有后继的记录，中间记录是更新。可以检测到记录与其前任记录的比较。

It is very easy to get a snapshot of a single entity (or the whole table) at a given point of time.

在给定的时间点获取单个实体（或整个表）的快照非常容易。

As a bonus, the syntax of this pattern for SQL Server happens to be the simplest one, compared to Oracle, DB2 and MySQL.

作为奖励，与Oracle，DB2和MySQL相比，SQL Server的这种模式的语法恰好是最简单的。

#7

do you really need to log the statement that ran, most people log the changed data (INSERTED and DELETED tables within the trigger).

你真的需要记录运行的语句，大多数人记录更改的数据（触发器中的INSERTED和DELETED表）。

#8

Triggers are a good way to ensure that any changes are logged, since they will almost always fire regardless of how the updates are performed - e.g. ad-hoc connections as well as application connections.

触发器是确保记录任何更改的好方法，因为无论更新如何执行，它们几乎总是会触发 - 例如， ad-hoc连接以及应用程序连接。

As suggested by @mwigdahl, the system management views look like a good way to capture the current running batch. Whether that's particularly useful to log in the trigger is another thing.

正如@mwigdahl所建议的那样，系统管理视图看起来是捕获当前正在运行的批处理的好方法。这对于登录触发器是否特别有用是另一回事。

One downside to using triggers is that you can only identify the source of the update from the database connection. Many applications don't have any user information associated with the connection, to facilitate connection pooling, so you don't know which user is performing the action. ie the Login used by the connection is a generic application login rather than the person using the application. The normal way to get around this is to use stored procedures as the interface to all database interaction, and then ensure that a UserId is passed with all procedure calls. You can then perform your logging via the stored procedure instead of a trigger. Clearly this is only useful if you know people won't update tables directly without using the procedures, or don't need to log that situation.

使用触发器的一个缺点是您只能从数据库连接中识别更新源。许多应用程序没有与连接关联的任何用户信息，以方便连接池，因此您不知道哪个用户正在执行该操作。即连接使用的登录名是通用应用程序登录名，而不是使用该应用程序的人。解决此问题的常用方法是使用存储过程作为所有数据库交互的接口，然后确保UserId与所有过程调用一起传递。然后，您可以通过存储过程而不是触发器执行日志记录。显然，只有当您知道人们不使用这些过程直接更新表，或者不需要记录这种情况时，这才有用。

The ability to get the currently executing batch might provide an even better mechanism: if you ensure that all your sql batches contain a UserId you could then extract this from the sql within your trigger. That would allow you to do all logging with triggers, which means you capture everything, but also let you associate changes with a particular user.

获取当前正在执行的批处理的能力可能提供更好的机制：如果您确保所有sql批处理包含UserId，则可以从触发器中的sql中提取它。这将允许您使用触发器执行所有日志记录，这意味着您捕获所有内容，但也允许您将更改与特定用户相关联。

If you're going down the trigger route it's worth checking the situations triggers aren't fired (maybe bulk loaded data? or if people have permission to disable triggers).

如果您正在按下触发路径，那么值得检查情况触发器是否被触发（可能是批量加载数据？或者如果人们有权禁用触发器）。

Also consider as @idstam pointed out that trigger code will be within your transaction so will normally be logged and rolled back along with it.

还要考虑@idstam指出触发器代码将在您的事务中，因此通常会记录并随之回滚。

Another thing to consider when writing triggers is the behaviour of @@IDENTITY: if you have procedures using @@IDENTITY you might accidentally change their behaviour.

编写触发器时要考虑的另一件事是@@ IDENTITY的行为：如果你有使用@@ IDENTITY的程序，你可能会意外地改变他们的行为。

#9

Use a Log Trigger

使用日志触发器

There is little reason to capture the actual SQL as there can many different statements that change data in the same way.

几乎没有理由捕获实际的SQL，因为可以有许多不同的语句以相同的方式更改数据。

#10

Try installing some trigger based third party tool such as ApexSQL Audit and then reverse engineering how they do it. Just install it in trial mode and see how it generates triggers for capturing all different sorts of information.

尝试安装一些基于触发器的第三方工具，例如ApexSQL Audit，然后对它们的工作方式进行逆向工程。只需将其安装在试用模式下，看看它如何生成触发器以捕获所有不同类型的信息。

Several other things to consider are:

其他几个需要考虑的事项是：

Storage planning – if you have a lot of updates that means you’ll have a ton of audit data. I’d consider storing that data in separate databases. Especially if you plan on auditing more than one database.

存储规划 - 如果您有大量更新，则意味着您将获得大量审计数据。我会考虑将这些数据存储在不同的数据库中。特别是如果您计划审核多个数据库。

Managing the amount of data – over time you probably won’t be in a need to keep some very old records. Plan on easy deletion of old data

管理数据量 - 随着时间的推移，您可能不需要保留一些非常旧的记录。计划轻松删除旧数据

Schema changes – what if schema is updated. In worst case your triggers will stop working and throw an error if not created correctly. At best case you’ll miss some of the data. This is also something to consider.

架构更改 - 如果架构更新会怎样。在最坏的情况下，如果没有正确创建，您的触发器将停止工作并抛出错误。在最好的情况下，你会错过一些数据。这也是需要考虑的事情。

Taking all of this into account it is probably the most time effective to go with some already developed solution vs creating this from scratch yourself.

考虑到所有这些因素，使用一些已经开发的解决方案与自己从头开始创建这个解决方案可能是最有效的。

#11

Be careful here, since triggers fire at the ROW level, not the SQL STATEMENT level. So, if someone does "DELETE FROM BIGTABLE", your trigger will fire for each row in that table (this specifically in regard to the fact that you want to know the SQL statement that performed the operation, so you'll need to "figure that out" for every row the statement affects).

这里要小心，因为触发器在ROW级别触发，而不是SQL STATEMENT级别。因此，如果某人“删除了BIGTABLE”，那么你的触发器将触发该表中的每一行（这特别是关于你想知道执行该操作的SQL语句的事实，所以你需要“图”说出“对于声明影响的每一行”。