SQL Server中的表历史记录触发器？ [英] Table history trigger in SQL Server?

问题描述

我想创建一个触发器，该触发器将使用插入的值以及更新前后的值写入历史记录表。我还要尽可能多地提供有关该帐户的信息。我将如何在触发器中包含帐户信息？

I'd like to create a trigger that writes to a history table with inserted values and before and after update values. I would also like to include as much information about the account doing the update as is possible. How would i include the account information in my trigger?

这是我到目前为止的内容：

Here is what I have so far:

CREATE TRIGGER [update_history] ON MyTable
FOR UPDATE
AS
INSERT MyTable_History (id, BudgetNumber, PositionNumber, ModifiedDate, action, userId)
SELECT id, BudgetNumber, PositionNumber, GETDATE(), 'BEFORE UPDATE', '???'
FROM deleted

INSERT MyTable_History (id, BudgetNumber, PositionNumber, ModifiedDate, action, userId)
SELECT id, BudgetNumber, PositionNumber, GETDATE(), 'AFTER UPDATE', '???'
FROM inserted

我可以代替'???'吗？

What do i put in place of the '???'?

推荐答案

如果每个如果用户有一个帐户，则可以使用 SYSTEM_USER 函数确定当前用户。但是，如果您的所有连接都通过代理帐户（大多数网站设置中通常是这样）进行，则您必须依靠将正确的userId传递给Update语句：

If each user has an account, you can use the SYSTEM_USER function to determine the current user. However, if all your connections go through a proxy account, as is typical in most web site setups, then you have to rely on the proper userId being passed to the Update statement:

CREATE TRIGGER [update_history] ON MyTable
FOR UPDATE
AS
INSERT MyTable_History (id, BudgetNumber, PositionNumber, ModifiedDate, action, userId)
SELECT id, BudgetNumber, PositionNumber, GETDATE(), 'BEFORE UPDATE', inserted.userId
FROM MyTable
    Join inserted
        On inserted.id = MyTable.id

INSERT MyTable_History (id, BudgetNumber, PositionNumber, ModifiedDate, action, userId)
SELECT id, BudgetNumber, PositionNumber, GETDATE(), 'AFTER UPDATE', userId
FROM inserted

这篇关于SQL Server中的表历史记录触发器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！