Using Triggers for Detective Work

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