来自 DML 触发器的 SQL 语句 [英] SQL Statement from DML Trigger

问题描述

我如何知道哪个 sql 语句是通过触发器对表的选择、插入、更新和删除触发的?

How can i know which sql statement fired through trigger for select, insert, update and delete on table?

推荐答案

正如 Jonas 所说，Profiler 是您的最佳选择(也是 SELECT 查询的唯一选择).对于 INSERT、UPDATE、DELETE，在没有 Profiler 的情况下最接近的可能是通过 DBCC INPUTBUFFER(@@SPID) 查看输入缓冲区.这仅适用于临时语言事件，不适用于 RPC 调用，并且只会显示 SQL 语句的前 256 个字符(我相信取决于版本).一些示例代码，(以 dbo 身份运行):

As Jonas says, Profiler is your best option (and only option for SELECT queries). For INSERT, UPDATE, DELETEs, the closest you can get without Profiler may be to look at the input buffer via DBCC INPUTBUFFER(@@SPID). This will only work for ad-hoc language events, not RPC calls, and will only show you the first 256 characters of the SQL statement (depending on version, I believe). Some example code, (run as dbo):

CREATE TABLE TBL (a int, b varchar(50))
go

INSERT INTO TBL SELECT 1,'hello'
INSERT INTO TBL SELECT 2,'goodbye'
go

GRANT SELECT, UPDATE ON TBL TO guest
go

CREATE TABLE AUDIT (    audittime datetime default(getdate())
                    ,   targettable sysname
                    ,   loginname sysname
                    ,   spid int
                    ,   sqltext nvarchar(max))
go

CREATE TRIGGER TR_TBL ON TBL FOR INSERT, UPDATE, DELETE
AS BEGIN
    CREATE TABLE #DBCC (EventType varchar(50), Parameters varchar(50), EventInfo nvarchar(max))

    INSERT INTO #DBCC
    EXEC ('DBCC INPUTBUFFER(@@SPID)')

    INSERT INTO AUDIT (targettable, loginname, spid, sqltext)
        SELECT  targettable =   'TBL'
        ,       suser       =   suser_name()
        ,       spid        =   @@SPID
        ,       sqltext     =   EventInfo 
        FROM #DBCC
END
GO

/* Test the Audit Trigger (can be run as guest) */
UPDATE TBL SET a = 3 WHERE a = 2

这篇关于来自 DML 触发器的 SQL 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！