July 22, 2008 at 8:33 am
hi all,
we are having a table consisting of 2 lac records, now we need a way by which we can come to know the row/rows that has been updated in last 24 hrs using T-sql statement or by any other means, can this be possible. pls advise.
thanks,
Vinit Fichadia
July 22, 2008 at 8:37 am
Does your table have a time-stamp column?
July 22, 2008 at 8:39 am
You would need to implement some way of tracking this yourself. The most common way is to add a "ModifiedDate" column to your tables an a trigger on the tables to set it whenever and Insert or Update is executed.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 22, 2008 at 11:12 pm
no timestamp column is there in my tables
July 22, 2008 at 11:20 pm
Vinit,
1st add a new column as ModifiedDate in your table and update all past records ModifiedDate values as you wants (Any Last date).
Then again come to design of ModifiedDate column and Make this column as GETDATE() as default value.
Now onwards you can able to find out who has modified the data for the table.
Cheers!
Sandy.
--
July 23, 2008 at 12:16 am
If in a situation, wherein it is not possible to change the schema, use ur logic to checkout the same.
One of the way is -
If ur database is relatively small, take a backup every fortnight and restore it to a database and then compare ur current database and the restored database, using tools like the SQL Toolbelt from Redgate, u will find the difference.
If ur database is relatively big, u can use differential backups.
This method will give you the modified data till the point of compare.
Another way -
You can have triggers and another table to store the row's ID and the date.
Another way -
You can have an on-demand transactional replication setup for the database. You can use tools like the SQL Toolbelt from Redgate to compare ur databases. When analysis is over and u are done, run the replication to keep your replicated database ready for the next compare.
This method will give you the modified data till the point of compare.
Another way -
If you are using SQL 2005, you can have scripts to create database snapshots at your desired intervals. Then you can use tools like the SQL Toolbelt from Redgate to compare ur databases. When analysis is over and u are done, delete the snapshots not required any further.
If by any chance you can change the schema for the table, include a column for a timestamp or date. In any case having a timestamp or a date column will be the most convenient option.
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
July 23, 2008 at 12:25 am
Vinit,
As Chandrachurh said, you can also use "Trigger" to do it...:)
Cheers!
Sandy.
--
July 23, 2008 at 12:32 am
Sandy (7/22/2008)
Then again come to design of ModifiedDate column and Make this column as GETDATE() as default value.
Actually, a default value is insufficient as this will have no effect when an existing record is Updated. You must use a Trigger to do this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 23, 2008 at 12:41 am
Yes, thats true...:)
Cheers!
Sandy.
--
July 24, 2008 at 6:46 am
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply