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.

There is a potentially bigger problem here, which is that your trigger appears to be recursive, because an update on `dim_Location` fires a trigger which again updates that same table. Is this the intended logic? — Tim Biegeleisen, Sep 03 '19 at 15:15
Use the alias in your `UPDATE` clause, however, why are you not referencing `inserted` here? This will update the **entire** table every time you update even a single row; surely that isn't wanted. Seems like you want an `INSTEAD OF` trigger here, really. — Thom A, Sep 03 '19 at 15:17

score 1 · Accepted Answer · answered Sep 03 '19 at 15:24

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

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

1 Answers1