need help regarding finding rows modifeid during certain time frame

[abhi.10dulkar@gmail.com]

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]

Re: need help regarding finding rows modifeid during certain time frame

(abhi.10dulkar@ 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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx