创建触发器来记录影响表的 SQL? [英] Create Trigger to log SQL that affected table?

问题描述

我试图找出更新列值的内容，但我对该应用程序知之甚少.快速浏览一下，我注意到大约 90% 的应用程序业务逻辑是在数据库上处理的.毋庸置疑，SP、功能和触发器的深度是疯狂的.

I'm trying to find out what is updating the value of a column and i have very little knowledge of the application. At a quick glance I've noticed about 90% of the applications business logic is handled on the database. Needless to say the depth of SP's, functions, and triggers is crazy.

我想在有问题的表上创建一个触发器，以记录影响该表的 SQL.可以使用什么 SQL 来获取正在更新的表的上下文中执行的 SQL?

I'd like to create a trigger on the table in question that will log the SQL that affected the table. What SQL could be used to grab the executed SQL in the context of the table being updated?

详情:微软 SQL Server 2008

Details: MS SQL Server 2008

谢谢！！

推荐答案

我的解决方案

我在有问题的表上添加了一个触发器，记录了我通过 sys.dm_exec_sql_text 和 sys.dm_exec_query_stats 的时间戳缩小范围的信息.这很快就确定了我正在寻找的东西.结果发现有一些我不知道的触发器是在 UPDATE 后更新数据.

My Solution

I added a trigger on the table in question that logged information i narrowed down via timestamps from sys.dm_exec_sql_text AND sys.dm_exec_query_stats. This quickly nailed down what i was looking for. Turns out there were a few triggers i didn't know about that were updating data after a UPDATE.

SELECT 
    qStats.last_execution_time AS [ExecutedAt],
    qTxt.[text] AS [Query], qTxt.number
FROM
    sys.dm_exec_query_stats AS qStats
    CROSS APPLY
    sys.dm_exec_sql_text(qStats.sql_handle) AS qTxt
WHERE
    qTxt.[dbid] = @DbId
    AND qTxt.[text] like '%UPDATE%'
    AND qStats.last_execution_time between @StartExecutionSearchTime and @EndExecutionSearchTime
ORDER BY
    qStats.last_execution_time DESC 

这篇关于创建触发器来记录影响表的 SQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！