Technical Article

Using Triggers for Detective Work

,

I used this trigger instead of a trace to identify how and where a column was being truncated in an essential table. The error never happened on test systems, only ever on the production server.

The trigger shows how to log changes only when a certain column is updated.

The data logged enabled me to locate the source of the error, and once that was corrected, both trigger and temporary table can be dropped.

-- first create a temporary table to Hold the results
-- 
CREATE TABLE [dbo].[Tmp_LogRangeRanges](
-- Copy of the original table, where a column was being truncated
[pKey] [bigint],
[$timeStamp] [datetime] NULL,
[fRange] [bigint] NULL,
[region] [varchar](32) NULL,
[startLoc] [int] NULL,
[endLoc] [int] NULL,
[project] [varchar](300) NULL,
-- Additional columns for identifying what caused the error
[ChangeDate] datetime Default GetDate(),
[OldNew] varchar(20),
[Machine] sysname,
[User] sysname,
[APP] sysname )

GO
-- The error was very specific.  The region column was being truncated
-- This caused errors further on in the process, which were traced back to
-- the truncated column.  
-- I set up the trigger and waited for the next occurence of the error
-- Then I could examine the temporary table and pinpoint the machine/
-- user and application that caused the truncation
CREATE TRIGGER u_LogRangeRanges ON dbo.RangeRanges FOR UPDATE
AS
DECLARE @OLD varchar(32), @NEW varchar(32)
SELECT @OLD = region FROM DELETED
SELECT @NEW = region FROM INSERTED
IF @OLD <> @NEW
BEGIN
INSERT INTO Tmp_LogRangeRanges
(pKey, [$timestamp], fRange, region, startLoc, endLoc, project, OldNew,
Machine, [User], APP)
SELECT pKey, [$timestamp], fRange, region, startLoc, endLoc, project, 'OLD',
HOST_NAME(), SUSER_SNAME(), APP_NAME()
FROM DELETED
INSERT INTO Tmp_LogRangeRanges
(pKey, [$timestamp], fRange, region, startLoc, endLoc, project, OldNew,
Machine, [User], APP)
SELECT pKey, [$timestamp], fRange, region, startLoc, endLoc, project, 'NEW',
HOST_NAME(), SUSER_SNAME(), APP_NAME()
FROM INSERTED
END
GO

Rate

3.5 (2)

Share

Share

Rate

3.5 (2)