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

(abhi.10dulkar@xxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Update Identity Column
    ... Actually, I implemented the trigger, and it seems to be working fine. ... Sylvain Lafontaine, ing. ... SQL Server will almost invariably decide that that's the best order to ... INSERT INTO tbl VALUES; ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Trigger For DateTime
    ... > I figured a trigger was the way to do this but haven't gotten the ... CREATE TRIGGER tbl_tri ON tbl FOR INSERT, ... UPDATE tbl ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Trigger
    ... I think the point of my trigger is supposed to keep the country ... CREATE TRIGGER no_more_teams ON tbl AFTER INSERT, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • waiting for trigger
    ... Is there any default time period in matlab for waiting for trigger before acquiring data? ...
    (comp.soft-sys.matlab)
  • Re: how does trigger works?
    ... errors if a ROLLBACK is performed. ... your insert time period. ... > I have an application that INSERT new records to a database at a rate of 5 ... > Is it wise to use a TRIGGER to perform this method? ...
    (microsoft.public.sqlserver.programming)