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
Linear Mode
