捕获数据库表的变化 DBCC INPUTBUFFER sp

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

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