捕获数据库表的变化 -DBCC INPUTBUFFER sp_executesql

最新推荐文章于 2020-10-23 23:14:41 发布

weixin_30551963

最新推荐文章于 2020-10-23 23:14:41 发布

阅读量114

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/jes_shaw/archive/2009/09/17/1568565.html

版权

http://www.experts-exchange.com/Database/Miscellaneous/Q_24712470.html

DBCC INPUTBUFFER sp_executesql

If I use the DBCC INPUTBUFFER, I get the statement run as sp_executesql;1 which does not tell me about the actual statement executed.

I cannot use fn_get_sql as well, because I am firing a trigger and fn_get_sql will only catch the trigger statement not the stament which fired the trigger.

Please help me in determining the actual statement which is being run or may be the object id of the statement being run through dbcc inputbuffer.

Code Snippet:

 
    1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
create TABLE DMLSOURCE 
(DML_ID int identity(1,1),
DML_CURRENT_USER nvarchar(100),
DML_SPID int,
DML_HOST_NAME nvarchar(100),
DML_APPNAME nvarchar(100),
DML_DATE datetime,
DML_TSQL nvarchar(max))
go
 
create TRIGGER TRG_DMLSOURCE
ON tblCustomer
FOR UPDATE
AS
IF UPDATE(tFirstName)
AND (SELECT COUNT(*) FROM INSERTED) > 0
BEGIN
SET NOCOUNT ON;
DECLARE @TSQL nvarchar(max)
DECLARE @IPB TABLE
(EVENTTYPE nvarchar(30),
PARAMETERS int,
EVENTINFO nvarchar(4000))
 
INSERT INTO @IPB
EXEC('DBCC INPUTBUFFER('+@@Spid+')')
SELECT @TSQL = EVENTINFO FROM @IPB
SELECT * from @IPB
 
INSERT INTO DMLSOURCE
(DML_CURRENT_USER, DML_SPID, DML_HOST_NAME, DML_APPNAME, DML_DATE, DML_TSQL)
VALUES
(CONVERT(nvarchar(100), CURRENT_USER),
@@spid,
host_name(),
app_name(),
getdate(),
@TSQL)
 
END 
 
GO
 
   

转载于:https://www.cnblogs.com/jes_shaw/archive/2009/09/17/1568565.html