Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    5

    Unanswered: Determining the SQL statement that triggered a trigger

    I am trying to set up my first trigger, and it's mostly there, but I'm having a little trouble. The trigger will log all deletes on a table, along with the SQL statement that did the delete. But I'm having trouble getting the SQL statement that triggered the delete correctly. When I use DBCC INPUTBUFFER(@@SPID), it returns the correct statement, but only the first 255 characters, which isn't long enough. When I use fn_get_sql, it returns the SQL of the trigger creation instead of the SQL that triggered the delete. Here is what my trigger looks like:


    create trigger deletion_trigger on mytable
    for delete
    as
    begin
    set nocount on

    declare @DelDate char(8),
    @DelTime char(12),
    @Handle binary(20),
    @Qry nvarchar(4000)

    select @Handle = sql_handle FROM master..sysprocesses WHERE spid = @@SPID
    SELECT @Qry = convert(nvarchar(4000),[text]) FROM ::fn_get_sql(@Handle)
    set @DelDate = convert(varchar(8), getdate(), 112)
    set @DelTime = convert(varchar(12), getdate(), 114)

    insert into deletelog values (@DelDate, @DelTime, @Qry)
    end


    I've read several similar questions to this on the net, but none of them seemed to ever be answered. Does anyone have any ideas on this?

    Thanks,
    Andy

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ajreynolds View Post
    When I use DBCC INPUTBUFFER(@@SPID), it returns the correct statement, but only the first 255 characters, which isn't long enough.
    Are you sure your issue is not that the display is defaulting to 255 characters?
    Run the LEN() function on the data, or in Management Studio check the Options/Query Results/SQL Server/Results to Text/Maximum number of characters displayed in each column setting.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2009
    Posts
    5
    Quote Originally Posted by blindman View Post
    Are you sure your issue is not that the display is defaulting to 255 characters?
    Run the LEN() function on the data, or in Management Studio check the Options/Query Results/SQL Server/Results to Text/Maximum number of characters displayed in each column setting.
    Thanks, but it is limited to 255. I did check the length and also the documenation specifically states it's 255 characters. That's why I'm trying to get fn_get_sql to work instead.

    Andy

  4. #4
    Join Date
    Nov 2009
    Posts
    5
    I've got my answer from another site I posted to. I don't actually have a problem after all. The server I was testing on is actually SQL Server 2000sp3, on which the DBCC INPUTBUFFER command only returns the first 255 characters of the command. SQL 2005 and 2008 don't have this problem will return the entire string. The real server I will be working on is 2005, so it's not a problem.

    The fn_get_sql function apparently has a known bug where it can't return the right value within a trigger the way I was trying to do it, so I'll just use DBCC INPUT BUFFER.

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •