Getting Last Query Statement

  • How can I get the last event query statement for specified table. Is it Update or Insert or Delete ?

  • - you could log it using triggers

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I used Trigger Event. But I didn't know which event (update/delete/insert) triggered. That' why if I check the query statement like the 'Trace Text Data', I'll know that exactly. Or I want to know the row and column which trigger event occured. How can I know it?

    Edited by - MyatSuu on 12/29/2003 01:27:50 AM

  • are you just trying to use Profiler for troubleshooting or do you want to know at all time which action(s) take place on this table ?

    Regarding the sqlserver-table-triggers you could create a trigger for insert, update, delete on that table and write the actions to a logging-table. that logging table then could contain a datetime column and a user-name column and an action column and ofcourse the keycolumns of your table so you could find out what action a certain user would have done on your table.

    check "CREATE TRIGGER" in BOL. (nice examples)

    Edited by - alzdba on 12/29/2003 01:51:59 AM

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thanks for reply me. I don't want to use Profiler. I want to use Trigger instead. And then I want to store the sql statement executed by user into the logging table.

    eg. Text Data Column in Profiler ....

    "Update Table1 Set Col1=..."

    Please Help me.

  • This may get messy but I'll give it a shot.

    TEST-it TEST-it TEST-it and use it wizely.

    It's still Monday-AM

    (don't know if I would use this way in a production env - you'll need to check it's overhead)

    ------

    use pubs

    create table t_Audit_SQLLog

    ( dtSQL datetime not null default getdate()

    , UserId sysname not null default suser_sname()

    , SQLstmt nvarchar(3000) not null )

    create proc dbo.usp_Audit_Authors

    as

    set nocount on

    create table #tmp_DBCC_InputBuff (EventType varchar(125), Parameters int, EventInfo nvarchar(3000))

    declare @ExecDBCC varchar(25)

    set @ExecDBCC = 'dbcc inputbuffer(@@spid)'

    insert into #tmp_DBCC_InputBuff

    exec(@ExecDBCC)

    Insert into t_Audit_SQLLog (SQLstmt)

    select EventInfo from #tmp_DBCC_InputBuff

    drop table #tmp_DBCC_InputBuff

    go

    --

    CREATE TRIGGER tr_IUD_Audit_Authors

    on authors

    FOR INSERT, UPDATE, DELETE

    AS

    set nocount on

    exec dbo.usp_Audit_Authors

    go

    INSERT INTO dbo.authors( au_id, au_lname, au_fname, phone, address, city, zip, contract)

    VALUES('123-45-6789', 'alzdba', 'sme', '123456789012', 'myadres', 'mycity', '00000', 0)

    go

    SELECT dtSQL, UserId, SQLstmt FROM dbo.t_Audit_SQLLog

    Edited by - alzdba on 12/29/2003 02:56:11 AM

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • That's Great. Thanks you so much for your help. Your advice is suitable for my problem.

  • To avoid sqlstatement-length you might consider to use text or ntext datatype for the SQLstmt-column, because when your trigger fails, your transaction fails, so your applications will be affected.

    Glad I could help.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 8 posts - 1 through 7 (of 7 total)

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