How to Track T-SQL Updates

You can use a trigger that will be executed on updates. Check https://www.aspsnippets.com/Articles/Simple-Insert-Update-and-Delete-Triggers-in-SQL-Server-with-example.aspx

>>Is there a way to determine that,  through some sort of tracking mechanism, the table has been updated and when?
TRIGGER is one way but in that you case you have to design all your system manually. You need to create a table , using trigger you need to insert the data into the logging table..etc.. Note - triggers are very difficult to manage.

Another option is CHANGE DATA CAPTURE
https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server
In this you need to use Enable Change Data Capture for a Table.  With this SQL Server will handle everything for you.
Sample below...

-- =============  
-- Enable a Table for All and Net Changes Queries template   
-- =============  
USE MyDB  
GO  
EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'MyTable',  
@role_name     = N'MyRole',  
@supports_net_changes = 1  
GO  

Select allOpen in new window

Another option is CHANGE TRACKING
https://msdn.microsoft.com/en-us/library/cc305322(v=sql.110).aspx

Éric Moreau is correct that you should use triggers.  The other methods involve far more complexity / difficultly in use and overhead than you need.  

However, the sample triggers in the link are horribly wrong and inaccurate for SQL Server.  Specifically, triggers using variables to load key values should never be used in SQL Server.  SQL Server fires a trigger only once per statement, no matter how many rows are deleted.  Using a variable would mean you would get only the "last" key value, even if 100 rows (or 1M rows) were UPDATEd.

To implement the trigger, first you'll need to create a table to hold the change history.  You'll want to include the column(s) that make up the key / identifying column(s) of the table.  Since I don't know the details of your table, I've made up 2 keys.  Adjust them as needed to match your actual table.

Then the trigger itself, to capture the previous column value, who made the change, the datetime of change (or more or less, depending on what you need/want).

Note: It's just a coincidence that change_type = 1 means the column was changed to 1.  The main thing is to use a code to indicate the change being tracked rather than using free-form text in a column.  That type of thing is *extremely* complex to parse thru later.


CREATE TABLE dbo.your_table_changes (
    key_col1 int NOT NULL,
    key_col2 int NOT NULL,
    change_date datetime NOT NULL DEFAULT GETDATE(),
    suser_sname nvarchar(128) NULL,
    previous_column_value varchar(10) NULL,
    change_type tinyint NOT NULL
    )
CREATE TABLE dbo.your_table_change_types (
    change_type tinyint NOT NULL PRIMARY KEY,
    description varchar(200) NOT NULL
    )

INSERT INTO dbo.your_table_change_types VALUES(1, 'Column name changed to "1".')


CREATE TRIGGER your_table__TRG_UPDATE
ON dbo.your_table
AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(column_name)
BEGIN
    INSERT INTO dbo.your_table_changes ( key_col1, key_col2, change_date,
        suser_sname, previous_column_value, change_type )
    SELECT  i.key_col1, i.key_col2, GETDATE(),
        SUSER_SNAME(), d.column_name, 1
    FROM inserted i
    INNER JOIN deleted d ON d.key_col1 = i.key_col1 AND d.key_col2 = i.key_col2
    WHERE i.column_name = '1' AND (d.column_name IS NULL OR d.column_name <> '1')
END /*IF*/

CDC is extremely complex to fully set up and use.  MS functions are so cumbersome and get so many errors I had to rewrite some of them.  I hope it works out for you.