创建触发器以记录影响表的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?

详细信息: MS 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屋！