need help regarding finding rows modifeid during certain time frame

[abhi.10dulkar]

Hi guys,

This might be simplest thing, but I am newbie to databases.

I need to find out only rows modified within certain time period from a
database. As I undertand a way out could be adding an where clause for
the time period might be an option, I might be wrong here again.

But, wanted to know is there any other option. Can triggers or any
other things help me in this matter.

Regards,
Abhijeet

[Erland Sommarskog]

(ab***********@ gmail.com) writes:

This might be simplest thing, but I am newbie to databases.

I need to find out only rows modified within certain time period from a
database. As I undertand a way out could be adding an where clause for
the time period might be an option, I might be wrong here again.

But, wanted to know is there any other option. Can triggers or any
other things help me in this matter.

First of all, the table(s) need to have a column that reports when
a row was last modified. (Or you need to have full-blown audit table
which tracks all modifications.)

Indeed, a trigger could be used to maintain such a column:
CREATE TRIGGER modified_tri ON tbl FOR INSERT, UPDATE AS
UPDATE tbl
SET last_modified = getdate()
FROM tbl t
WHERE EXISTS (SELECT *
FROM inserted i
WHERE i.keycol = t.keycol)
Once there, the filtering is just one condition in the WHERE clause when
you select.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx