How to get last insert/update/delete datetime on S

问题:

not a duplicate of my previous question

Is there any way to get the latest datetime when a table/database had an insert/update/delete on Sql Server 2005? Preferably without creating triggers..

I know that when you need the last update per row, you need triggers. But I am not sure if they are needed when you just want to get the last update for the whole table.

回答1:

You Can Easily Get the Last Inserted/Updated/Deleted Dates as Follows:

CREATE FUNCTIOn fn_TablesLastUpdateDate(@Date NVARCHAR(20))

RETURNS @table TABLE(TableName NVARCHAR(40), LastUpdated Datetime)

AS

BEGIN


IF(@Date='') OR (@Date Is Null) OR (@Date='0')

    BEGIN
        INSERT INTO @table
        SELECT TOP 100 PERCENT TABLENAME,LASTUPDATED FROM 
        (
            SELECT  B.NAME AS 'TABLENAME', MAX(STATS_DATE (ID,INDID)) AS LASTUPDATED
            FROM    SYS.SYSINDEXES AS A
                    INNER JOIN SYS.OBJECTS AS B ON A.ID = B.OBJECT_ID
            WHERE   B.TYPE = 'U'  AND STATS_DATE (ID,INDID) IS NOT NULL 
            GROUP BY B.NAME
        ) AS A
        ORDER BY LASTUPDATED DESC
    END
ELSE

    BEGIN
        INSERT INTO @table
        SELECT TOP 100 PERCENT TABLENAME,LASTUPDATED FROM 
        (
            SELECT  B.NAME AS 'TABLENAME', MAX(STATS_DATE (ID,INDID)) AS LASTUPDATED,
                    CONVERT(VARCHAR, MAX(STATS_DATE (ID,INDID)), 103) as Date
            FROM    SYS.SYSINDEXES AS A
                    INNER JOIN SYS.OBJECTS AS B ON A.ID = B.OBJECT_ID
            WHERE   B.TYPE = 'U'  AND STATS_DATE (ID,INDID) IS NOT NULL 
            GROUP BY B.NAME
        ) AS A
        WHERE Date=@Date
        ORDER BY LASTUPDATED DESC
    END
RETURN

END



-- SELECT * from fn_TablesLastUpdateDate('06/11/2012')

回答2:

SELECT t.name,
       user_seeks,
       user_scans,
       user_lookups,
       user_updates,
       last_user_seek,
       last_user_scan,
       last_user_lookup,
       last_user_update
FROM   sys.dm_db_index_usage_stats i
       JOIN sys.tables t
         ON ( t.object_id = i.object_id )
WHERE  database_id = DB_ID() 

回答3:

As the previous two answers show, there's really no built-in functionality in SQL Server that's readily available for your requirements.

There are a ton of dynamic management views which can tell you some of your points of interest, e.g. sys.dm_db_index_usage_stats which tells you when a given index has had its last seek or update.

But there's really nothing in the box per se that you could leverage to get all the information you're looking for - you really have to do this yourself, adding e.g. datetime fields to your tables and filling them with triggers.

Sorry I can't give you any better news - that's just the way it is for now.

In SQL Server 2008, you have additional new features, that might cover some of your requirements - check out:

• Change Data Capture
• SQL Server Auditing

Marc

回答4:

Given there are no answers yet, here are my 2 cents:

• For Insert, I would use a DateTime field with default value GETDATE()
• For Update, I would use also a DateTime field modified by trigger each time there is an update.
• For Delete, the record won't be available, so you can't query it.

I thought about using Timestamps for avoinding triggers, but you can't convert Timestamp to Datetime.

EDIT: Or maybe you can use a "metatable" where in the triggers you will save the change dates

CREATE TABLE metatable (
table_name VARCHAR(40) NOT NULL PRIMARY KEY,
last_insert DATETIME NOT NULL,
last_update DATETIME NOT NULL,
last_delete DATETIME NOT NULL
)

INSERT metatable VALUES ('table1', GETDATE(), GETDATE(), GETDATE())

CREATE TRIGGER trg_table1_ins ON table1 FOR INSERT AS BEGIN
    UPDATE metatable SET last_insert = GETDATE() WHERE table_name = 'table1'
END

CREATE TRIGGER trg_table1_upd ON table1 FOR UPDATE AS BEGIN
    UPDATE metatable SET last_update = GETDATE() WHERE table_name = 'table1'
END

CREATE TRIGGER trg_table1_del ON table1 FOR DELETE AS BEGIN
    UPDATE metatable SET last_delete = GETDATE() WHERE table_name = 'table1'
END

I hope it be useful

回答5:

Over short periods (since server startup) you check sys.dm_db_index_usage_stats last_user_update column. But since this only counts updates since server startup, it cannot be used over a long period of time.

For long periods of time, if the table is not huge, your application can store the table CHECKSUM_AGG(ALL). You'd only need to recompute this once, at application start up, and compare it with the previously stored value. Further the application can detect changes using the DMV. At application shutdown it should store the current table checksum.

回答6:

without triggers: you could have a LastChgDate column on the table, and set it when you insert/update/delete a row. You'd have to "delete" by usig a status column set to "D" or something like that. Put an index on this column and select the MAX() to see when a change was made.

回答7:

Well, you could keep a column with a "LastUpdateDate" that is set to the current server date/time on any insert or update. Then you can simply query for the row with the most recent LastUpdateDate.

回答8:

I would add Change Tracking to the mix - as opposed to Change Data Capture, it is not an Enterprise only feature.

Read about it at http://msdn.microsoft.com/en-us/library/cc280462.aspx

Similar to sys.dm_db_index_usage_stats, the data is not there forever (though it survives server restarts and is configurable) and you'll have to extract and persist the particular piece of information you're looking for.

MDD

回答9:

Simple! First add a "LastUpdated" column. Give it the default of GetDate(). This will take care of Insert Statements. Second, add an On Update Trigger that updates LastUpdated to GetDate(). Updates are now covered. Finally add a bit/boolean field IsDeleted with the default of 0. Should a user want to delete a row, flip the bit. Since when you "delete" a row, you are actually updating the IsDeleted field (and therefore are using an Update action), Deletes are now timestamped.

To get the most recent activity on the table: to only get the timestamp:

SELECT MAX(LastUpdated) FROM MyTable

To get more information:

SELECT MAX(LastUpdated), ID /*or whatever you need to know*/ FROM MyTable