Trigger performance - UPDATE statement joining on INSERTED
as the number of records increase in TABLE_B, trigger performance degrades rapidly


That would imply the updates do not have appropriate indexes available, or are unable to use them for some reason, on TABLE_B or the tables that it is joined to.

Without more detail about the table layout (what indexes you have on each of the tables) and the query plan that SQL has decided to use we can't help you in much detail, so I suggest you update the question with that information.

Some first thoughts:


  FROM TABLE_C C INNER JOIN INSERTED I ON C.UNIQUE_ID = I.UNIQUE_ID AND C.USER_ID = I.USER_ID


As you say the performance is tied to TABLE_B this is probably OK, though again the query plan would be helpful information to provide. Presumably you have an index on TABLE_C.UNIQUE_ID or it is the primary key or subject to a unique constraint.


  INNER JOIN [TABLE_B] B ON B.UNIQUE_ID = C.UNIQUE_ID AND B.USER_ID = C.USER_ID


With the right indexes/keys this should not be an issue either. For maximum efficient for this specific statement TABLE_C should have an index covering UNIQUE_ID &amp; USER_ID and TABLE_B one covering UNIQUE_ID &amp; USER_ID and including ATTRIBUTES.


  WHERE B.[ATTRIBUTES] &amp; 4 = 0


This may be significant. Depending on how TABLE_B and TABLE_C are getting joined (again, provide the index details and query plan) this could be forcing a table scan of TABLE_B. The expense of that operation would grow at linearly with the number of rows in TABLE_B.