Hello Tom,

it may have worked in your particular environment, but I don't think it is a good idea to post this trigger in an article... unless it is an article "What to avoid when writing triggers".

Even at first glance, this trigger has a major problem, because it only works if ONE row is updated. What happens if you update 10 or 500 rows at once? Even if your applications always update one row, any triggers should be written so that they can handle any amount of rows in one update. You can never know what will happen in some future version of some of the applications, and of course there can be some "extra" access (like update issued from QA by DBA). Also, you are doing 2 inserts in the trigger, while the same can be accomplished with one.

IMHO, it would be better to write the check this way:

CREATE TABLE [dbo].[Tmp_LogRangeRanges](

-- Copy of the original table, where a column was being truncated, with columns for new and old value

[pKey] [bigint],

[$timeStamp_old] [datetime] NULL,

[fRange_old] [bigint] NULL,

[region_old] [varchar](32) NULL,

[startLoc_old] [int] NULL,

[endLoc_old] [int] NULL,

[project_old] [varchar](300) NULL,

[$timeStamp_new] [datetime] NULL,

[fRange_new] [bigint] NULL,

[region_new] [varchar](32) NULL,

[startLoc_new] [int] NULL,

[endLoc_new] [int] NULL,

[project_new] [varchar](300) NULL,

-- Additional columns for identifying what caused the error

[ChangeDate] datetime Default GetDate(),

[Machine] sysname,

[User] sysname,

[APP] sysname )

CREATE TRIGGER u_LogRangeRanges ON dbo.RangeRanges FOR UPDATE

AS

BEGIN

INSERT INTO Tmp_LogRangeRanges

(pKey,[$timestamp_old], fRange_old, region_old, startLoc_old, endLoc_old, project_old

[$timestamp_new], fRange_new, region_new, startLoc_new, endLoc_new, project_new,

Machine, [User], APP)

SELECT i.pKey, d.[$timestamp], d.fRange, d.region, d.startLoc, d.endLoc, d.project,

i.[$timestamp], i.fRange, i.region, i.startLoc, i.endLoc, i.project,

HOST_NAME(), SUSER_SNAME(), APP_NAME()

FROM DELETED d

JOIN INSERTED i ON i.pKey=d.pKey

WHERE d.Region <> i.Region

END

GO

It is quite probable that others will come up with even better solution, I was just trying to do minimum changes in your code to achieve reliable result. I hope I didn't mistype something, the code is not tested.