How to Create Trigger to Keep Track of Last Changed Data

Question

CREATE TABLE Member
(
    memberID - PK
    memberName
    dateRegistered - one time process
);

CREATE TABLE MemberLastChanged
(
   memberID 
   memberName
   dateEntered
);

If by any chance a user changes his member name, i need to keep track of the currently changed memberName in a history table.

For example, current info is:

memberID: 5534
memberName: james

User changes it to:

memberID: 5534
memberName: mark

By now, "Member" will hold current values:

5534 and mark

AND

"MemberLastChanged" will hold:

5534 and james

How can i achieve this in t-sql using trigger?

Possible duplicate of http://stackoverflow.com/questions/1962398/creating-audit-triggers-in-sql-server — Michael Freidgeim, Jul 01 '13 at 13:02

score 4 · Answer 1 · answered Jan 01 '11 at 12:23

CREATE TRIGGER TRG_Member_U ON Member FOR UPDATE
AS
SET NOCOUNT ON

INSERT MemberLastChanged (memberID, memberName)
SELECT
   D.memberID, D.memberName
FROM
   DELETED D JOIN INSERTED I ON D.memberID = I.memberID
WHERE
   D.memberName <> I.memberName
GO

Also, add a default of GETDATE to dateRegistered so it's recorded automatically.

This also filters out dummy updates by comparing new and old values (INSERTED vs DELETED).

INSERTED and DELETED are special tables available only in trigger.

Oded · Accepted Answer · 2011-01-01T19:43:36.140

3

You create an UPDATE trigger - triggers have access to two logical tables that have an identical structure to the table they are defined on:

INSERTED, which is the new data to go into the table
DELETED, which is the old data the is in the table

See this MDSN article on using these logical tables.

With this data you can populate your history table.

CREATE TRIGGER trg_Member_MemberUpdate
ON dbo.Member AFTER UPDATE
AS
    INSERT INTO dbo.MemberLastChanged(memberID, memberName)
    SELECT d.MemberID, d.MemberName
    FROM DELETED d

edited Jan 01 '11 at 19:43

answered Jan 01 '11 at 12:21

Oded

463,167
92
837
979

I just took off "FOR", the code works perfectly fine. I picked yours because there are no conditionals which i believe would faster in heavy load performance (if any) ... If i'm wrong regarding the performance, please inform me. Cheers – user311509 Jan 01 '11 at 19:41
@user311509: if you have many dummy updates and the client doesn't trap this you'll have a massive history table with useless data. Interesting you're optimising *already* before you have a problem... – gbn Jan 02 '11 at 12:57

score 2 · Answer 3 · answered Jan 01 '11 at 12:22

You want to have an AFTER UPDATE trigger on your users table - something like:

CREATE TRIGGER trg_MemberUpdated
ON dbo.Member AFTER UPDATE 
AS BEGIN
   IF UPDATE(memberName)
      INSERT INTO 
          dbo.MemberLastChanged(memberID, memberName, dateEntered)
          SELECT
             d.MemberID, d.MemberName, GETDATE()
          FROM 
             Deleted d
END

Basically, this trigger checks to see whether the memberName property was updated; if so, a row with the old values (which are available in the Deleted pseudo table inside the UPDATE trigger) is inserted into MemberLastChanged

How to Create Trigger to Keep Track of Last Changed Data

3 Answers3

Linked