May 3, 2005 at 11:28 pm
Dear all
I need to know the what are the UPDATE/INSERT statement issue in a some tables in my database.
When ever the user issue update/insert in these tables , I need the following information.
sql statement, which host executing these stmts, wht time,login name, throw which application this stmts executed
When ever any update or inset happen these tables these information have to write in a table or in a test file.
I try 'sql trace' and sql trace is only giving me the “Select” stmts.and it is not provide me the option for specific tables. I read that sql trace is not for log period.
How can I implement these option in the DB? Is any tool is SQL server is supporting for these requirement.
Pls advice
Mathew
May 4, 2005 at 10:25 am
You can setup triggers to audit things. ApexSQL has a tool to automate this.
Profiler will catch the inserts/updates.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 5, 2005 at 6:59 am
Maybe this example will give you some ideas:
/*
For each INSERT and UPDATE, log the following:
1. sql statement
2. hostname
3. time: GETDATE()
4. login name,
5. ??? assume app name ??? "throw which application this stmts executed"
*/
GO
DROP TABLE tblTest
DROP TABLE tblLog
GO
CREATE TABLE tblTest
(
id int PRIMARY KEY IDENTITY(1,1)
, myData varchar(20)
)
GO
CREATE TABLE tblLog
(
id int PRIMARY KEY IDENTITY(1,1)
, sqlcmd varchar(256)
, hostName varchar(50)
, execTime datetime DEFAULT GetDate()
, loginName varchar(50)
, applName varchar(50)
)
GO
CREATE TRIGGER triu_tblTest
ON tblTest
FOR INSERT, UPDATE
AS
DECLARE @inputBuffer varchar(255)
CREATE TABLE #InputBuffer
(
EventType varchar(15)
, Parameters int
, EventInfo varchar(255)
)
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
INSERT #InputBuffer EXEC ('dbcc inputbuffer( @@spid ) WITH NO_INFOMSGS ')
INSERT tblLog (sqlcmd, hostName, execTime , loginName , applName)
SELECT (SELECT TOP 1 EventInfo FROM #InputBuffer) AS sqlCmd
, hostname
, last_batch
, loginame
, program_name
FROM master.dbo.sysprocesses WHERE spid = @@spid
DROP TABLE #InputBuffer
GO
-----------------------------------------
--- TEST IT
-----------------------------------------
TRUNCATE TABLE tblLog
TRUNCATE TABLE tblTest
SET NOCOUNT ON
INSERT tblTest (myData) VALUES ('First Row')
INSERT tblTest (myData) VALUES ('Second Row')
INSERT tblTest (myData) VALUES ('Third Row')
INSERT tblTest (myData) VALUES ('Fourth Row')
INSERT tblTest (myData) VALUES ('Fifth Row')
SET NOCOUNT OFF
PRINT ''
PRINT 'LOG after initial INSERTs'
PRINT ''
SELECT * FROM tblLog
UPDATE tblTest SET myData = 'UPDATED:' + myData WHERE id IN (1,3,5)
PRINT ''
PRINT 'LOG after UPDATEs'
PRINT ''
SELECT id, hostName, execTime , loginName , applName , sqlcmd
FROM tblLog
ORDER BY id
GO
May 6, 2005 at 6:50 am
Dear All,
Thanks for your suggestion and all of u r suggestion is valid.
Stave Jones can u pls give some links or information how the Profiler will catch the inserts/updates.
Waiting for u r reply
Mathew
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply