Post reply

Query text to get in insert/update/delete trigger

  • July 18, 2008 at 4:49 am

    #194075

    All,

    I want to get the text of query that is just inserted/updated/delete (DML Statements). Query text that causes the trigger to fire.

    For e.g. I have an after insert trigger trig1 on table table1

    If insert query executed for table1 say "insert into table1 values ('AnyValue')" that will cause trig1 to fire. Inside that trigger I want to get the query text/insert statement that caused the trigger to fire.

    How can I?

  • July 18, 2008 at 5:48 am

    #844941

    [font="Verdana"]Turn on SQL Profiler then.

    Mahesh[/font]

    MH-09-AM-8694

  • July 18, 2008 at 6:04 am

    #844953

    i want to get the query inside that trigger! (programatically)

  • July 18, 2008 at 6:51 am

    #844979

    I'm not sure there is a way. You can get the currently executing statement from the sys.dm_exec_sql_text funtion, getting the sql_handle from sys.dm_exec_connections or sys.dm_exec_sessions, but that will just show you the definition of the trigger.

    The SQL engine doesn't keep track of what statements a user executed in the past, just the ones it's running now.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 18, 2008 at 7:00 am

    #844988

    I have tried using the following tables but still not able to get the result

    SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]

    FROM sys.dm_exec_query_stats AS deqs

    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

    ORDER BY deqs.last_execution_time DESC

    but this shows the information related to Create procedures and select, no DML records found

    even if I can get the last executed DML statement then that will be fine!

  • July 18, 2008 at 7:04 am

    #844993

    Query stats has no user-related info in. If you query that for the most recent statement, you'll get the most recent statement by any user on the server, not by your connection.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 18, 2008 at 12:36 pm

    #845208

    That is not the problem for me as I am doing all this for auditing purpose rather thats good if I am getting user information too. Simply I want to log whole query that raise the trigger and thats it. How?

  • July 20, 2008 at 11:31 pm

    #845493

    I don't think you can, unless you have some profiler or a server-side trace running.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 20, 2008 at 11:37 pm

    #845500

    Why do you want the query text in the trigger.

    "Keep Trying"

  • July 20, 2008 at 11:51 pm

    #845523

    For generic auditing purpose!

  • December 30, 2008 at 12:42 pm

    #919211

    Select definition

    from sys.all_sql_modules m

    join sys.objects o on m.object_id = o.object_id

    where o.type in ('TR','TA')

    and o.name = 'YourTriggerName'

  • September 15, 2011 at 2:57 am

    #1381762

    Try: DBCC INPUTBUFFER (@@SPID)

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply