Copy Tuple OR save Id of Tuple in another table (ProductLogs) when any field is updated in a specific table (products)

Question

I don't know the solution I'm finding, will be possible or not.

Problem Statement: There is a table named Products in DB, I need to keep track of products with price updated/modified, but I don't want to use modified_date column because it doesn't exist.

A senior resource asked me to run a query in SQL server DB, whenever that table will be modified that record will be copied to your log/track table, so I can find from there.

Answer 1

Try to use trigger after UPDATE:

    CREATE TRIGGER ModDate
    ON YourTable
    AFTER UPDATE
AS
BEGIN        
    INSERT INTO dbo.LogTable(DateModified)
    VALUES (GETDATE());
    PRINT 'New Block Date Added';
END 

UPDATE:

When trigger works, under the hood there are two tables:

• "INSERTED"
• "DELETED"

The data in these virtual tables depends what you are doing:

• Insert operation: when you insert a record, virtual table "inserted" contains the newly inserted record ,where "DELETED" virtual table remains empty

• Update operation: when you update a record, at first the old record will be placed into the "DELETED" virtual table and the newly updated record is hold by the "INSERTED" virtual table.

  That means you can get the old value from "DELETED" and the currently updating value through "INSERTED" virtual table. you can query them like:

  -- To get the old record value
  SELECT * FROM DELETED
  
  -- To get the updated value
  SELECT * FROM INSERTED
  

• Delete Operation: When try to delete any particular record the deleted record will be inserted into the "DELETED" virtual table.

So your trigger should look like this:

CREATE TRIGGER ModDate
    ON YourTable
    AFTER UPDATE
AS
BEGIN        
    INSERT INTO dbo.LogTable(DateModified, IdUpdatedRow)
    SELECT GETDATE(), INSERTED.YourIdOfUpdatedRow;
    PRINT 'New Block Date Added';
END