Geek Answers Handbook

After pasting, check the timestamp box with two primary column keys

I have a simple data table, for example:

listid custid status last_changed

The primary key consists of listid and custid .

Now I'm trying to set up a trigger that sets the last_changed column at the current time and time with every insert or update. I found a lot of information on how to do this with a single PK column, but with multiple PKs it gets confusing how to correctly specify PK from an INSERTED table.

The trigger should work in SQL Server 2005/2008 / R2.

Thanks for the working launch code!

The bonus will also check whether the data has really been changed and updated only in the latter case, but in order to really understand how to correctly code the main question, which I would like to see as a separate block of code, if for all.

+4
source share
3 answers

Hmm .... just because the primary key is made up of two columns that shouldn't matter much ....

 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 set the JOIN between the two tables (your own data table and the Inserted pseudo-table) on both columns ...

Am I missing something? .....

+8
source 
  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 am assuming that the stasus column is not NULL. If yes, you should add extra code to check if one of the columns is NULL

+1
source

You can check each field in a trigger by comparing data from an inserted and deleted table, as shown 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
0
source

More articles:


All Articles