SQL Server 中的表历史触发器?

2022-01-01 00:00:00 sql-server triggers

我想创建一个触发器，用插入的值和更新前后的值写入历史表.我还想尽可能多地包含有关执行更新的帐户的信息.我如何在触发器中包含帐户信息?

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?

这是我目前所拥有的:

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函数来确定当前用户.但是，如果您的所有连接都通过代理帐户进行，这在大多数网站设置中很常见，那么您必须依赖传递给 Update 语句的正确用户 ID:

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

相关文章