After insert, update timestamp trigger with two column primary key

Question

I have a simple details table like so:

listid
custid
status
last_changed

The primary key consists of both listid and custid.

Now I'm trying to setup a trigger that sets the last_changed column to the current datetime every time an insert or update happens. I've found lots of info on how to do that with a single PK column, but with multiple PKs it gets confusing on how to correctly specify the PKs from the INSERTED table.

The trigger has to work in SQL Server 2005/2008/R2.

Thanks for a working trigger code!

Bonus would be to also check if the data was actually altered and only update last_changed in that case but for the sake of actually understanding how to correctly code the main question I'd like to see this as a separate code block if at all.

Answer 1

Hmm.... just because the primary key is made up of two columns shouldn't really make a big difference....

CREATE TRIGGER dbo.trgAfterUpdate ON dbo.YourTable
AFTER INSERT, UPDATE 
AS
  UPDATE dbo.YourTable
  SET last_changed = GETDATE()
  FROM Inserted i
  WHERE dbo.YourTable.listid = i.listid AND dbo.YourTable.custid = i.custid

You just need to establish the JOIN between the two tables (your own data table and the Inserted pseudo table) on both columns...

Are am I missing something?? .....

Answer 2

    CREATE TRIGGER dbo.trgAfterUpdate ON dbo.YourTable
    AFTER INSERT, UPDATE 
    AS
      UPDATE dbo.YourTable
      SET last_changed = GETDATE()
      FROM Inserted i
      JOIN dbo.YourTable.listid = i.listid AND dbo.YourTable.custid = i.custid
    WHERE NOT EXISTS
(SELECT 1 FROM Deleted  D Where D.listid=I.listid AND D.custid=i.custid AND (D.status=i.status) 

Here i assuming that stasus column is not nullable. If yes, you should add additional code to check if one of columns is NULL