how to check if table in mssql had been modified-CodePudding

What it's the fastest way to check if table or row in mssql(using nodejs) has been modified?

I need to check if my db had been updated in the last 30 min.

if it's not has been updated in the last 30 min, i will send an email to the client with a lead.

CodePudding user response：

You could add "created_at" and "modified_at" columns to your table. Then you just update "modified_at" everytime you make changes to your data. Now you have updated information about when this data was modified.

Now if you want to check if there are any modifications in any records in the last 30 minutes you could create cronjob that runs certain query. I don't have any example query because I am not so good in SQL. But now you can get started with this information.

CodePudding user response：

You can create the following function to find the last time any update was done against a table:

create function dbo.TableLastUpdated(@schema sysname, @name sysname)
returns datetime as
begin
return (
    select Max(last_user_update) 
    from sys.tables t
    join sys.dm_db_index_usage_stats s on s.object_id = t.object_id
    where t.name = @name and t.schema_id=Schema_Id(@schema)
)
end

Then simply use it

select dbo.TableLastUpdated('dbo','Table Name')

Note this information is only available since the last time the server was started.