SQL Server触发器-按事务分组 [英] SQL Server Triggers - grouping by transactions

问题描述

在工作中，我们刚刚开始为数据库建立审计框架（即记录创建或更新数据时已更改的数据）。

At work we have just started building an auditing framework for our database (i.e. to log what data has changed when it is created or updated).

我很想使用触发器来实现此目的，因为有时数据会从其他地方导入到系统中，而不仅仅是通过前端。这似乎是一个相当常见的解决方案。

We'd quite like to implement this using triggers as data sometimes gets imported into the system from other places, not just via the front-end. This seems to be a fairly common solution.

但是我想添加一个：如果用户运行的更新会更新多个表，我希望将它们成批分组（即

However I'd like to make one addition: if a user runs an update which updates more than one table, I'd like those to be grouped together in a batch (i.e. to generate a unique batch ID for each set of updates).

问题是要从触发器中获取一个批次ID。每次更新都将在事务中运行，因此我想知道是否可以使用事务ID生成批次ID。但是我找不到在T-SQL中任何地方访问事务ID的方法。

The problem is, getting a batch ID from within a trigger. Each update will run within a transaction, so I was wondering whether I could use the Transaction ID to generate a batch ID. However I can't find a way of accessing the Transaction ID anywhere within T-SQL.

有人有什么想法吗？

PS -我们正在运行SQL Server 2008，如果有什么不同

P.S. - We are running SQL Server 2008, if it makes any difference

推荐答案

您可以只使用
select * from sys .dm_tran_current_transaction

you can just use select * from sys.dm_tran_current_transaction

sys .dm_tran_current_transaction

这篇关于SQL Server触发器-按事务分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！