2

I need to know when the data in a table was last modified (data inserted, created, deleted). The common answer is to extract the data from sys.dm_db_index_usage_stats, but I don't have access to that table and as I understand it, access to that is a server-level permission, and since this is on a corporate shared server, the odds of the IT department granting me access is up there with pigs flying.

Is there a way to get this information, that does not require greatly elevated privileges?

Update #1

Some additional information: What I am doing, is caching the contents of this table locally, and need to know when to update my local cache. This means:

  1. I don't need an actual timestamp, just any value that I can compare against a locally-cached value that tells me "things have changed, update the cache"
  2. If this value changes too often (e.g. gets reset every time they restart the server which is extremely rarely) it's OK, it just means I do an extra cache update that I didn't actually need to do

Update #2

I should have done this early on, but I just assumed I would be able to create tables as needed ... but I'm not. Permission not granted. So neither of the proposed methods will work :-(

Here's my new thought : I can call

select checksum_agg(binary_checksum(*))

and get a checksum on the entire remote table. Then, if the checksum changes, I know the table has changed and I can update my local cache. I've seen the problems with checksum, but using HASHBYTES sounds like it would be much more complicated and much slower.

This is working, the problem is that when the checksum changes, I have to reload the entire cache. My table has so many rows that returning the checksum per row takes an unacceptably long time, is there a way to use the "OVER" clause and get maybe 10 checksums, the first checksum for the first tenth of the rows, etc.?

Community
  • 1
  • 1
Betty Crokker
  • 3,001
  • 6
  • 34
  • 68
  • I'm assuming your table doesn't have anything like a `LastModified` column? – Siyual Jul 11 '16 at 14:59
  • Correct! And, it's a table created and maintained by a third party product so while I could modify their schema to add such a column, I don't have a way to modify their code to maintain such a column. – Betty Crokker Jul 11 '16 at 15:11
  • Well if you're happy modifying the schema you could add a trigger to log these operations. – Martin Smith Jul 11 '16 at 15:15
  • I was thinking about that ... odds are good I could add such a trigger without messing up the operation of the third party product, but it makes me a little nervous. If I can't find any other way that might be the only solution ... – Betty Crokker Jul 11 '16 at 15:17
  • @BettyCrokker Does the table currently contains ant TimeStamp(or RowVersion) column? This column can also help you achieve the same result. If it does not have any, you can add one. Read more about this datatype:https://msdn.microsoft.com/en-us/library/ms182776.aspx – Vahid Farahmandian Jul 11 '16 at 15:28
  • I didn't know about that datatype, thanks for the information! I am thinking though (correct me if you disagree) that adding a column is more likely to confuse the third party product than adding a trigger? – Betty Crokker Jul 11 '16 at 15:39
  • Problem with `rowversion` nee 'timestamp` is that it doesn't provide a "when" in terms of date and time. http://stackoverflow.com/questions/8119386/how-to-convert-sql-servers-timestamp-column-to-datetime-format – Shannon Severance Jul 11 '16 at 16:03
  • Most third-party vendors I've dealt with do not support modifications to their schemas. You can do it, but I imagine somewhere in the legal agreements they'll no longer be obligated to provide support when some issue pops up. I've personally seen this arise during an application upgrade where a customization caused the process to fail and the vendor effectively said, "good luck." – Jacob Barnes Jul 11 '16 at 17:53

1 Answers1

1

If you can modify their schema, then you should be able to add a trigger.

Once you add a lastmodified column, you can use this trigger to get the time updated any time the record changes:

CREATE trigger [dbo].[TR_TheirTable_Timestamp]
on [dbo].[TheirTable] for update
as 
begin
    update
        dbo.TheirTable
    set
        lastmodified=getdate()
    from
        Inserted
    where
        dbo.TheirTable.UniqueID=Inserted.UniqueKey
end

The reason I do it only for update, and not insert is because I can see a new record, and I don't need a timestamp, but a modified record I can compare the time I last updated the record. if you want an insert update, then

on [dbo].[TheirTable] for insert,update

would work

If you just wanted to know when the table was updated, then the trigger could write to another table with the tablename and date, and you wouldn't have to modify their schema

SeanC
  • 15,695
  • 5
  • 45
  • 66