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