How to create a trigger that automatically update a datetime column when a field of the table is updated

Question

i am trying to create a column that measures the UPDATE_DATE of a row. I am building a trigger but i have an ambiguity problem regarding the key.

CREATE TRIGGER ModDate_location_update
    ON dim_Location
    AFTER UPDATE
AS
BEGIN
    UPDATE dbo.dim_Location 
    SET UPDATE_DATE = GETDATE()
    --WHERE INSERT_DATE IN (SELECT INSERT_DATE FROM INSERTED)
    FROM dim_Location X
    JOIN dim_Location Y ON X.NEW_LOCATION_KEY = Y.NEW_LOCATION_KEY
end

Msg 8154, Level 16, State 1, Procedure ModDate_location_update, Line 6 [Batch Start Line 119] The table 'dbo.dim_Location' is ambiguous.

Answer 1

Simply update all the rows having a PK that appears in the INSERTED virtual table. EG:

CREATE TRIGGER ModDate_location_update
    ON dim_Location
    AFTER UPDATE
AS
BEGIN
    UPDATE dbo.dim_Location 
    SET UPDATE_DATE = GETDATE()
    WHERE LOCATION_ID IN (SELECT LOCATION_ID FROM INSERTED)
end