如何检查MS SQL中事务后更新的最后一个表 [英] How to check last table which was updated after a transaction in MS SQL

问题描述

Hai All，



我想知道在MS SQL中从前端执行事务后哪个表已更新。



我检查了下面的代码..

但是没达到我的预期。



我想要桌子在交易后插入数据。



请帮帮我



我是什么尝试过：

Hai All,

I want to know which table got updated after doing a transaction from front end in MS SQL.

I checked the below code..
but didn't meeting my expectations.

I want the table in which data got inserted after doing a transaction.

please help me

What I have tried:

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query], dest.dbid
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

推荐答案

希望这会对你有所帮助。

此解决方案将有助于减少桌面数量



1.创建一个包含以下列的表格



TableName：LastUpdateTableInfo



1.Id - 主键（自动生成）

2.TableName

3.UpdateDate



2.为每张桌子创建AfterUpdate触发器

Hope this will help you.
This Solution will help for less tables

1.Create a Table with following columns

TableName : LastUpdateTableInfo

1.Id - primary key(Auto generate)
2.TableName
3.UpdateDate

2.Create the AfterUpdate Trigger for each tables

use [DatabaseName] 
Go
create trigger trgupdatetableInfo on [TableName]
for update
as
declare @tableName as varchar(50)
declare @lastUpdateTime as varchar(50)

set @tableName =[yourtablename]
set @lastUpdateTime= GETDATE()

insert into dbo.LastUpdateTableInfo (tablename,UpdateDate) values (@tableName,@lastUpdateTime)
Go

现在每当更新发生时，提及表都会记录信息。 br />


您可以通过提供以下查询来获取最后一个更新表名称

Now whenever update happens the mention table will log the info .

You can get the last update table name by supply the following Query

SELECT  [Id]
      ,[TableName]
      ,[UpdateDate]
  FROM [placeDB].[dbo].[LastUpdateTableInfo] order by Id desc

看看这个链接，希望这会对你有所帮助： SQL SERVER - 查找为任何表更新的上次日期时间 - 使用Pinal Dave访问SQL权限 [ ^ ]

Take a look at this link, hope this will help you: SQL SERVER - Find Last Date Time Updated for Any Table - Journey to SQL Authority with Pinal Dave[^]

这篇关于如何检查MS SQL中事务后更新的最后一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！