SQL Server 2005/8 复制事务 ID
我有一个场景，我使用事务复制将多个 SQL Server 2005 数据库（同一实例）复制到单个远程数据库（单独物理机上的不同实例）。
然后，我对复制的数据进行一些处理以用于报告目的。我使用表级触发器来识别对我的后处理代码执行哪些操作的更改。
到目前为止一切都很好。
然而，我想知道的是，在同一事务中创建、更新或删除某些表的地方，是否可以从复制（或任何地方）识别某种事务ID，这样我就不会执行相同的操作对单个事务进行多次后处理。
基本示例：我有一个 TUser 表和 TAddress 表。如果我要在单个事务中创建两者，它们也将在单个事务中复制。但是，复制数据库中会触发两个触发器 - 目前这会导致我的后处理代码运行两次。我真正想确定的是，这两个更改是在同一事务中复制的。
这有可能吗？我所描述的标识符是否存在并且可以访问吗？
I have a scenario where I'm using transactional replication to replicate multiple SQL Server 2005 databases (same instance) into a single remote database (different instance on a separate physical machine).
I am then performing some processing on the replicated data for reporting purposes. I'm using table level triggers to identify changes which actions my post processing code.
Up to this point everything is fine.
However, what I'd like to know is, where certain tables are created, updated or deleted in the same transaction, is it possible to identify some sort of transaction ID from replication (or anywhere) so then I don't perform the same post processing multiple times for a single transaction.
Basic Example: I have a TUser Table and TAddress table. If I was to create both in a single transaction, they would be replicated across in a single transaction too. However, there would be two triggers fired in the replicated database - which at present causes my post processing code to be run twice. What I'd really like to identify is that these two changes arrived in the replicated in the same transaction.
Is this possible in any way? Does an identifier as I've describe exist and is it accessible?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
简短的回答是否定的，没有什么可以信赖的。总而言之，长的答案是，是的，它存在，但不建议以任何方式将其用于任何用途。
鉴于复制在事务上是一致的，您可以考虑的一种方法是将主记录的标识符（在本例中为 TUser，因为 TAddress 与 TUser 相关）推送到队列中（使用类似 Service Broker 理想情况下或可能是 用户定义队列），然后通过从队列中弹出数据并单独处理来执行后处理。
另一种可能性是简单地每隔“x”时间进行批处理，通过从主表中轮询新的/更新的记录并以这种方式进行后处理 - 您需要跟踪 id、行版本或某种类型的时间戳。已将每个主表作为元数据进行处理，并在每次批处理运行期间提取尚未处理的任何内容。
只是一些想法，希望有所帮助。
Short answer is no, there is nothing of the sort that you can rely on. Long answer in summary would be that yes it exists, but it would not be recommended in any way to be used for anything.
Given that replication is transactionally consistent, one approach you could consider would be pushing an identifier for the primary record (in this case TUser, since an TAddress is related to TUser) onto a queue (using something like Service Broker ideally or potentially a user-defined queue) and then perform the post-processing by popping data off the queue and processing separately.
Another possibility would be simply batch processing every 'x' amount of time by polling for new/updated records from the primary tables and post-processing in that manner - you'd need to track id's, rowversions, or timestamps of some sort that you've processed for each primary table as meta-data and pull anything that hasn't yet been processed during each batch run.
Just a few thoughts, hope that helps.