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

Log changes to database table with trigger

提问by Jason

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 语句。

采纳答案by Andomar

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

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

If you are trying to troubleshoot something, attach Sql Profilerto 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.

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

回答by Juan Carlos Velez

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

回答by mwigdahl

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.

不过，我不确定这对您是否会像更以数据为中心的日志记录机制一样有用。

回答by idstam

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.

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

回答by user1309314

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

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

The changes are recorded on another historytable. 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 snapshotof 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 的这种模式的语法恰好是最简单的。

回答by ChrisW

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 有名为“Inserted”和“Deleted”的虚拟表，其中包含新插入和/或新删除和/或新更新的数据记录，您可以从触发器访问这些记录……我使用这些来了解什么数据已更改（而不是被告知哪个语句更改了数据）。

回答by KM.

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

您是否真的需要记录运行的语句，大多数人记录更改的数据（触发器中的插入和删除表）。

回答by grokster

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，因为可能有许多不同的语句以相同的方式更改数据。

回答by Rory

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 的过程，您可能会意外更改它们的行为。

回答by Joel Cuff

Try installing some trigger based third party tool such as ApexSQL Auditand 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.

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