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