Table history trigger in SQL Server?(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?
这是我目前所拥有的:
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
这篇关于SQL Server 中的表历史触发器?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:SQL Server 中的表历史触发器?
基础教程推荐
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- Sql Server 字符串到日期的转换 2021-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- SQL Server 2016更改对象所有者 2022-01-01
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01