How to Create Trigger to Keep Track of Last Changed Data

How to Create Trigger to Keep Track of Last Changed Data

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

Create trigger to keep the latest record

You need to use the :new keyword to differentiate with :old values. Also, better use AFTER trigger:

CREATE OR REPLACE TRIGGER TRIG_HISTORY 
AFTER INSERT ON source_table_name
    FOR EACH ROW
DECLARE

BEGIN
MERGE INTO dest_table_name d
    USING (select :new.price p, :new.product_id p_id from dual) s
    ON (d.product_id = s.p_id)
  WHEN MATCHED THEN
    UPDATE SET d.price = s.p
  WHEN NOT MATCHED THEN
    INSERT (price, product_id)
    VALUES (s.p, s.p_id);
END;

How can I structure a trigger to record previous values when updated?

You can use the "deleted" and "inserted" tables in your trigger: https://msdn.microsoft.com/en-us/library/ms191300(v=sql.110).aspx

CREATE TRIGGER Customer_update_trig ON Customer
AFTER UPDATE
AS
BEGIN
  INSERT INTO Customer_Audit (Cust_User, Cust_Update_Date, Cust_ID, CustomerAddresss, CustomerZipCode)
  SELECT d.Cust_User,
    d.Cust_Update_Date,
    d.Cust_ID,
    d.CustomerAddresss, --sic
    d.CustomerZipCode
  FROM deleted d
END

-- OR for auto generated user and timestamp

CREATE TRIGGER Customer_update_trig ON Customer
AFTER UPDATE
AS
BEGIN
  INSERT INTO Customer_Audit (Cust_User, Cust_Update_Date, Cust_ID, CustomerAddresss, CustomerZipCode)
  SELECT CURRENT_USER,
    GETDATE(),
    d.Cust_ID,
    d.CustomerAddresss, --sic
    d.CustomerZipCode
  FROM deleted d
END

Of course, you'll want to put your schema name in there too (or 'dbo' if using the default). And double-check the column names that they match your original table.

For your audit strategy, you'll want to decide if you only want to record after an update, or eagerly record the original insert also.

Another thing for SELECT optimization from your audit table, you can have much more efficient WHERE clauses if you have a Cust_Record_Start_Time and Cust_Record_End_Time to give a range for when the record was valid. It is much easier to query on a point in time with that data.

I don't have SQL Server anymore, so I wasn't able to test the syntax, but the concept should be there.

Additionally, if adding this trigger adds too much time to your UPDATE statements, you can look into Service Broker: https://msdn.microsoft.com/en-us/library/bb522893(v=sql.110).aspx

It allows for async guaranteed "messages" that could update your audit table in very near real time.

Trigger to track changes in MySQL Database

Had to remove Delimiter, Begin, and End statements.

---

ANSWER

DROP TRIGGER IF EXISTS insert_classes;
CREATE TRIGGER insert_classes AFTER insert ON Classes
FOR EACH ROW
insert into tracking_classes (command, classID, Title, classDesc, Category, isEvent, picLeft, picTop, picRight, picBottom, prnColor, modified)
values('insert', NEW.classID, NEW.Title, NEW.Desc, NEW.Category, NEW.isEvent, NEW.picLeft, NEW.picTop, NEW.picRight, NEW.picBottom, NEW.prnColor, NOW());

Trigger to update record when Value = GETDATE()

No, as far as I know, you can't use a trigger for this situation.
Triggers are executed, when data/records change.
In your case, the data is not changing, only time is passing.

Look for a 'SQL job' to accomplish this task.

Trigger whenever a value in a view has changed

In the comments, you confirmed that you want the following:

you want to update the "last_updated" column in table 1 whenever a
value changes in tables 1, 2 or 3, and include that "last_updated"
value in your view

This question shows you how to create a trigger to update a column when a record is modified.

You will need to create that trigger on tables 1, 2 and 3, and the update statement should update table 1's lastUpdated column.

Alternatively, you could create a lastUpdated on all 3 tables, and select the highest value in your view.

---

Related Topics