SQL Server 中的表历史触发器？

发布于 2024-10-03 08:36:35 字数 556 浏览 0 评论 0原文

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

这是我到目前为止所得到的：

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

我该用什么来代替“？？？”？

原文

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 '???'?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

忆沫 2024-10-10 08:36:35

如果每个用户都有一个帐户，您可以使用SYSTEM_USER函数来确定当前用户。但是，如果您的所有连接都通过代理帐户（就像大多数网站设置中的典型情况一样），那么您必须依赖传递到 Update 语句的正确 userId：

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

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

回复收藏 0 原文