SQL Statement from DML Trigger

Question

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

Thanks, Paresh Prajapati http:\paresh-sqldba.blogspot.com

Answer 1

First, there are no select dml triggers, only triggers that work on INSERT, UPDATE or DELETE

Secondly, you can't know which sql statement triggered the trigger (at least not in the trigger). However, you can use profiler to debug what's happening in the database. There's a decent explanation of this here.

Answer 2

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