I think your trigger model needs to look more like this.

because triggers handle multiple rows in SQL server, you should never declare a variable in a trigger, because it makes you think of one row/one value, instead of the set.

there are exceptions of course, but it's a very good rule of thumb.

also note the UPDATE function doesn't tell you the VALUE changed on a column...only whether the column was included int eh column list for insert/update. so if it was updated to the exisitng value (and a lot of data layers will do that automatically) it's a false detection of a change.

CREATE TRIGGER [TRG_TESTING]

ON TABLE_NAME

AFTER INSERT,UPDATE

AS

SET NOCOUNT ON

INSERT INTO SomeTrackingTable(ColumnList)

SELECT ColumnList

FROM INSERTED

LEFT OUTER JOIN DELETED

ON INSERTED.SomePrimaryKey = DELETED.SomePrimaryKey

WHERE DELETED.SomePrimaryKey IS NULL --inserted only

OR (INSERTED.SpecificColumn <> DELETED.SpecificColumn) --this column changed...so we need to log it.