After insert, update timestamp trigger with two column primary key

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 actuall开发者_开发知识库y understanding how to correctly code the main question I'd like to see this as a separate code block if at all.

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?? .....

    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

You can check every field in trigger by comparing data from inserted and deleted table like below :

    CREATE TRIGGER [dbo].[tr_test] ON [dbo].[table]
    AFTER INSERT, UPDATE
    AS 
    BEGIN
    DECLARE @old_listid INT
    DECLARE @old_custid INT
    DECLARE @old_status INT

    DECLARE @new_listid INT
    DECLARE @new_custid INT
    DECLARE @new_status INT

    SELECT @old_listid=[listid], @old_custid=[custid], @old_status = [status] FROM [deleted]

    SELECT @new_listid=[listid], @new_custid=[custid], @new_status = [status] FROM [inserted]

    IF @oldstatus <> @new_status
    BEGIN
        UPDATE TABLE table SET last_changed = GETDATE() WHERE [listid] = @new_listid AND [custid] = @new_custid 
    END

END