How to Create Trigger to Keep Track of Last Change

问题:

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?

回答1:

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

回答2:

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.

回答3:

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