SQL statement from DML Trigger

Question

How can I find out which sql statement was triggered through a trigger to select, insert, update, and delete in a table?

Answer 1

As Jonas says, Profiler is your best bet (and only an option for SELECT queries). For INSERT, UPDATE, DELETE, the closest you can get without a Profiler might be to look at the input buffer through DBCC INPUTBUFFER(@@SPID) . This will only work for special language events, not for RPC calls, and will only show the first 256 characters of the SQL statement (depending on version, I reckon). 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 

Answer 2

Firstly, there are no dml select triggers, only triggers that work with INSERT, UPDATE or DELETE

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