0

I am trying to get the last executed query and log it (as is, a varchar).

For example:

insert into table (firstname,lastname,birthdate) values ('donald','obama','1990-01-01')


create trigger dbo.logqueries
on dbo.users
for update, insert, delete
as
begin

    declare @query nvarchar(400)

    hopefully a simple method to recover the query as is 
    without having to re-prepare the query before insert

    insert into queryLog (userId,query) values (@userId,@query)
end

This is the result I got from trying McNets' answer:

DECLARE @TEMP TABLE 
(EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(4000)) 
INSERT INTO @TEMP EXEC('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS') 
set @query =(SELECT EventInfo FROM @TEMP)

inserted value

  • What are you trying to achieve with that trigger? You are clearly not recording any select queries, only DMLs, which is why you could simply create trigger for each operation and output values into audit table, along with the operation, and host_name. – S4V1N Aug 1 '17 at 11:55
3

Have a look at MS-Docs about DBCC INPUTBUFFER

Displays the last statement sent from a client to an instance of Microsoft SQL Server.

Add WITH NO_INFOMSGS just to suppress all informational messages.

Permissions

On SQL Server requires one of the following:

  • User must be a member of the sysadmin fixed server role.
  • User must have VIEW SERVER STATE permission. session_id must be the same as the session ID on which the command is being run.

CREATE TABLE Test (Id int PRIMARY KEY, Val varchar(10));
CREATE TABLE Audit (Id int IDENTITY PRIMARY KEY, EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(4000));
GO
CREATE TRIGGER AuditTest
ON Test
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    INSERT INTO Audit EXEC('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS');
END
GO
INSERT INTO Test VALUES(1, 'John');
GO
2 rows affected
INSERT INTO Test VALUES(2, 'Anna');
GO
2 rows affected
UPDATE Test SET Val='Peter' WHERE Id=1;
GO
2 rows affected
DELETE FROM Test WHERE Id=2;
GO
2 rows affected
SELECT * FROM Audit;
GO
Id | EventType      | Parameters | EventInfo                              
-: | :------------- | ---------: | :--------------------------------------
 1 | Language Event |          0 | INSERT INTO Test VALUES(1, 'John');    
 2 | Language Event |          0 | INSERT INTO Test VALUES(2, 'Anna');    
 3 | Language Event |          0 | UPDATE Test SET Val='Peter' WHERE Id=1;
 4 | Language Event |          0 | DELETE FROM Test WHERE Id=2;           

As sepupic has pointed out on his comments if you are using an stored procedure, DBCC INPUTBUFFER returns the EXEC sentence instead of the SQL statement.

CREATE PROCEDURE dbo.InsertTest(@Id int, @Val varchar(10))
AS
BEGIN
    INSERT INTO Test VALUES (@Id, @Val);
END
GO
EXEC dbo.InsertTest @Id=5, @Val='Peper';
GO
2 rows affected
Id | EventType      | Parameters | EventInfo                               
-: | :------------- | ---------: | :---------------------------------------
 1 | Language Event |          0 | INSERT INTO Test VALUES(1, 'John');     
 2 | Language Event |          0 | INSERT INTO Test VALUES(2, 'Anna');     
 3 | Language Event |          0 | UPDATE Test SET Val='Peter' WHERE Id=1; 
 4 | Language Event |          0 | DELETE FROM Test WHERE Id=2;            
 5 | Language Event |          0 | EXEC dbo.InsertTest @Id=5, @Val='Peper';

dbfiddle here

Let me suggest you another approach, instead of store SQL commands, you can keep track of all changes of your data on this way:

1- Create a duplicate of your table and add some audit fields like, action, date, user, etc.

CREATE TABLE Test (Id int PRIMARY KEY, Val varchar(10));
CREATE TABLE Audit (Action varchar(10), CreatedAt datetime, [User] nvarchar(128), Id int, Val varchar(10));
GO

2- Lets the trigger to store all new changes.

CREATE TRIGGER AuditTest
ON Test
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @Action varchar(10);
    IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
        SET @Action = 'UPDATE'
    ELSE IF EXISTS(SELECT 1 FROM inserted)
        SET @Action = 'INSERT'
    ELSE
        SET @Action = 'DELETE';

    IF @Action = 'UPDATE' OR @Action = 'INSERT'
    BEGIN
        INSERT INTO Audit
        SELECT @Action, GETDATE(), SUSER_NAME(), Id, Val
        FROM   inserted;
    END
    ELSE
    BEGIN
        INSERT INTO Audit 
        SELECT @Action, GETDATE(), SUSER_NAME(), Id, Val
        FROM   deleted;
    END
END 
GO

Now the final result will show something like this:

SELECT * FROM Audit;
GO
Action | CreatedAt           | User                                    | Id | Val  
:----- | :------------------ | :-------------------------------------- | -: | :----
INSERT | 01/08/2017 15:05:03 | fiddle_404da64bd81c4665970c0b39386771b0 |  1 | John 
INSERT | 01/08/2017 15:05:03 | fiddle_404da64bd81c4665970c0b39386771b0 |  2 | Anna 
UPDATE | 01/08/2017 15:05:03 | fiddle_404da64bd81c4665970c0b39386771b0 |  1 | Peter
DELETE | 01/08/2017 15:05:03 | fiddle_404da64bd81c4665970c0b39386771b0 |  2 | Anna 
INSERT | 01/08/2017 15:05:03 | fiddle_404da64bd81c4665970c0b39386771b0 |  5 | Peper

And it doesn't matter if you use a direct command, an stored procedure or you're calling it from an external application.

dbfiddle here

  • 1
    If op is on 2016, you could use dm_exec_input_buffer here instead. Lower privileges needed. – Erik Darling Aug 1 '17 at 12:14
  • 1
    When using stored procedure to insert/update/delete, DBCC INPUTBUFFER will return only procedure call (exec MySP), not the insert/update/delete statement – sepupic Aug 1 '17 at 12:26
  • i'm using SQL server 2008 – IWonderHowLongANameICanTypeInH Aug 1 '17 at 13:18
  • Expand it ,to include user who made those changes – S4V1N Aug 1 '17 at 13:58
  • @S4V1N have a look – McNets Aug 1 '17 at 14:07
1

This should hopefully work providing the user submitting has the correct permissions, you could hand this off to a stored proc maybe executing as higher level if needed. Get the most recent SQL statement from the current SPID. Might not always be 100% if lots have been executed at once but worth a go.

SELECT T.text
FROM sys.dm_exec_connections
    OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle) T
WHERE session_id = @@SPID;

You could pull more info from exec connections too if you wanted.

EDIT: This above seems to only return the trigger. For completeness you can get it working like this example i mocked up, you could include query stats and plans to be logged too...

USE General_POC
CREATE TABLE TRG_TEST (id int)
CREATE TABLE TRG_AUD (sqtT VARCHAR(MAX))

;CREATE TRIGGER trgtest
ON trg_test
FOR INSERT
AS
BEGIN

    WITH x
    AS (SELECT TOP 1
            T.text
        FROM sys.dm_exec_query_stats
            OUTER APPLY sys.dm_exec_sql_text(sql_handle) T
        WHERE T.text LIKE '%INSERT%TRG_TEST%'
              AND T.text NOT LIKE '%TRIGGER%'
              AND last_execution_time > DATEADD(SECOND, -30, GETDATE())
       )
    INSERT INTO dbo.TRG_AUD
    (
        sqtT
    )
    SELECT x.text
    FROM x

END;

INSERT INTO dbo.TRG_TEST
(
    id
)
VALUES (1 -- id - int
       )

SELECT *
FROM dbo.TRG_AUD

By getting the last insert into that table from sql queries submitted. However this isn't as efficient or accurate as other answers, but as i said if you want the extra info to log like plans etc this could be handy. It helps to see other thought processes for fun.

  • i am getting values like: >values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12) . any idea why ? – IWonderHowLongANameICanTypeInH Aug 1 '17 at 11:39
  • Ah where does the insert come from? I get that kind of value when data comes from Web connections using entity framework or similar. – Ollie Aug 1 '17 at 11:54
  • it's from a java EE webapp – IWonderHowLongANameICanTypeInH Aug 1 '17 at 12:54
  • Yeah from frame works like that it's just how the query is submitted to sql server i believe. I don't think it's possible to see it as a traditional sql statement unfortunately. – Ollie Aug 1 '17 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.