审核触发器中每个插入的行 [英] Audit each inserted row in a Trigger

问题描述

我试图通过向表中添加触发器并将行插入到我的审计表中来进行审计历史记录。我有一个存储过程，使插入操作变得容易一些，因为它可以节省代码。我不必写出整个插入语句，但是我可以使用要插入的列的一些参数来执行存储过程。

我不是确保如何对插入表中的每一行执行存储过程。我认为也许我需要使用游标，但不确定。我以前从未使用过游标。

由于这是一次审核，因此，我需要将旧列与新列的值进行比较，以查看它是否已更改。如果确实更改了，我将执行在我的Audit表中添加一行的存储过程。

有什么想法吗？

解决方案

我会为时间交换空间而不进行比较。只需在插入/更新时将新值推送到审核表即可。磁盘很便宜。

此外，我不确定存储过程能为您带来什么好处。您不能在触发器中做一些简单的事情吗？

 插入dbo.mytable_audit 
（选择*，getdate （），getdate（），从插入位置'创建'）
  

触发器在插入时运行，而您添加创建时间，上次更新时间和修改类型字段。对于更新，这有点麻烦，因为您需要提供命名参数，因为不应更新创建的时间

 插入dbo.mytable_audit（col1，col2，....，last_updated，修改）
（从插入位置选择*，getdate（），'update'）
  

此外，您是否还计划仅审核成功或失败？如果要审核失败，则需要触发器以外的其他东西，因为如果事务回滚，触发器将不会运行-如果触发器先运行，您将不具有事务的状态。 / p>

我实际上已将审核移至数据访问层，并立即在代码中进行。它使成功和失败审核更加容易，并且（使用反射）很容易将字段复制到审核对象。它允许我执行的另一件事是给用户上下文，因为我没有向实际用户授予数据库权限，而是使用服务帐户运行所有查询。

I am trying to do an audit history by adding triggers to my tables and inserting rows intto my Audit table. I have a stored procedure that makes doing the inserts a bit easier because it saves code; I don't have to write out the entire insert statement, but I instead execute the stored procedure with a few parameters of the columns I want to insert.

I am not sure how to execute a stored procedure for each of the rows in the "inserted" table. I think maybe I need to use a cursor, but I'm not sure. I've never used a cursor before.

Since this is an audit, I am going to need to compare the value for each column old to new to see if it changed. If it did change I will execute the stored procedure that adds a row to my Audit table.

Any thoughts?

解决方案

I would trade space for time and not do the comparison. Simply push the new values to the audit table on insert/update. Disk is cheap.

Also, I'm not sure what the stored procedure buys you. Can't you do something simple in the trigger like:

insert into dbo.mytable_audit
    (select *, getdate(), getdate(), 'create' from inserted)

Where the trigger runs on insert and you are adding created time, last updated time, and modification type fields. For an update, it's a little tricker since you'll need to supply named parameters as the created time shouldn't be updated

insert into dbo.mytable_audit (col1, col2, ...., last_updated, modification)
     (select *, getdate(), 'update' from inserted)

Also, are you planning to audit only successes or failures as well? If you want to audit failures, you'll need something other than triggers I think since the trigger won't run if the transaction is rolled back -- and you won't have the status of the transaction if the trigger runs first.

I've actually moved my auditing to my data access layer and do it in code now. It makes it easier to both success and failure auditing and (using reflection) is pretty easy to copy the fields to the audit object. The other thing that it allows me to do is give the user context since I don't give the actual user permissions to the database and run all queries using a service account.

这篇关于审核触发器中每个插入的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！