I recently discovered that some one (or application) updated all the
rows in the database. Looks like a query was missing a where clause.
It was probably one of the applications, but reviews of the code show
that a where clauses is always used, or so it appears. Anyway, I set
up a trigger to capture every update to a particular table that
recorded who did what and when. I created the trigger (on insert and
update) on a table and in it I use the new fn_get_sql function that
comes with SQL Server 2000 SP3. It looks like this:
----------
CREATE TRIGGER Update_Last_Modified ON [dbo].[MYTABLENAME]
FOR UPDATE, INSERT
AS
BEGIN
SET NOCOUNT ON
DBCC TRACEON (2861)
DECLARE @.Qry nvarchar(4000)
DECLARE @.handle binary(20)
SELECT @.handle = sql_handle
FROM master..sysprocesses
WHERE spid = @.@.SPID
SET @.QRY = (SELECT CONVERT(nvarchar(4000), [text]) FROM
::fn_get_sql(@.handle))
UPDATE MYTABLENAME
SET DATE_LAST_MODIFIED = GETDATE(),
LAST_COMMAND = @.QRY,
LAST_USER = SYSTEM_USER
FROM inserted
WHERE MYTABLENAME.UID= Inserted.UID
END
-----------
It was previously coded to use DBCC INPUTBUFFER, and it worked fine,
but I was limited to the first 255 characters of the command, which
prevented me from seeing the critical parts, like the where clause!
When I modified the trigger to use fn_get_sql, all I ever see is the
entire text of the create trigger command. Maybe I should use an
entirely different approach. I'm open to ideas.
Thanks very much in advance for your help!
Miles
_________________Miles (milesfeinberg@.hotmail.com) writes:
> It was previously coded to use DBCC INPUTBUFFER, and it worked fine,
> but I was limited to the first 255 characters of the command, which
> prevented me from seeing the critical parts, like the where clause!
> When I modified the trigger to use fn_get_sql, all I ever see is the
> entire text of the create trigger command. Maybe I should use an
> entirely different approach. I'm open to ideas.
Yes, the idea with fn_get_sql is to get the currently executing statement
of a procedure. And for a process that introspects itself, the current
statement will be the statement it queries sysprocesses. So in your
case DBCC INPUTBUFFER is a better bet.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Jackxxx:
