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
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.
3 Solutions collect form web for “After insert, update timestamp trigger with two column primary key”
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