I have an audit table EpisodesHistory. One of the fields that gets copied is eDescription. I expect eDescription to be regularly be a couple kilobytes in length.

For database space and transmission to client applications, I wanted to do something where if, upon recording the change, eDescription is identical to a previous iteration, it records that value.

Let’s say I have this EpisodesHistory table:

HistoryID(PK) | EpisodeID | eDescription | ... other fields 1               10          Watch my dog do a trick 2               10          Watch my cat sleep 3               11          Watch my dog do a trick 

In the case of row 3, it was inserted because the episodeID is different.

However, if I then added:

HistoryID(PK) | EpisodeID | eDescription | ... other fields` 4               10          Watch my dog do a trick` 

I wanted to auto-update an additional field EpisodesHistory.eDescRef to the ID of the row containing the same description. 1 in this case because 1 is the first row to have this field.

It took me a few attempts to get something working so I want to make sure that this is the optimum method as this will be one of the busier triggers in my database.

USE [Shows] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[tr-EpisodesHistory] on [dbo].[Episodes] FOR UPDATE AS  IF TRIGGER_NESTLEVEL(OBJECT_ID('dbo.EpisodesHistory')) > 1     BEGIN         RETURN;     END;  DECLARE @CMP INTEGER DECLARE @NEWID INTEGER = 0  IF NOT UPDATE(eOwner) OR NOT EXISTS((SELECT *, eActor = 0 FROM Inserted except SELECT *, eActor = 0 FROM Deleted))   --- the second condition was moved to the code-side query to prevent firing of the trigger BEGIN     -- Weed out updates to episode scoring, or where nothing that matters in the row was changed (eActor doesn't matter)     -- eVersion is incremented code-side, so this has to be undone.     -- Could be incremented here instead, but thats yet another query for successful updates     UPDATE Episodes       SET eVersion = eVersion - 1     WHERE EpisodeID = (SELECT EpisodeID From Inserted)     return; END  SET @CMP = (Select Top 1 Past.HISTORYID          FROM DELETED Curr, EpisodesHistory Past          where Curr.EpisodeID = Past.EpisodeID            and Curr.eOwner = Past.eOwner            and Curr.eDescription = Past.eDescription)  SET @CMP = ISNULL(@CMP, 0) INSERT into EpisodesHistory SELECT *, eDescRef = @CMP, hWhen = getDate() from deleted; SET @NEWID = SCOPE_IDENTITY()  IF @CMP > 0 BEGIN     UPDATE EpisodesHistory     SET eDescRef = @CMP,         eDescription = ''     WHERE HistoryID = @NEWID END