Re: How do I detect changes to a table?

There is one problem with the solution suggested below. The trigger will record the time that the trigger fired, not the time that the change was committed to the database. Other processes don't see the change until it's committed. Therefore, any process using the time of last change of a record to compare against the last time the table was processed will miss some records.

Consider this sequence of events:

At time 1 the writer changes record A of table B, and puts 1 in record A's "last changed time" field.

At time 2 the reader starts reading records from B that have been changed since the last time it read records, and records time 2 as the new "last read" time.

At time 3 the writer commits the change to record A. Note that the reader won't rea record A. The RDBMS guarantees that the results of the SELECT statement are the contents of the database at the moment that the SELECT starts. (This behaviour is what you want in the majority of applications.)

At time 4, the reader starts reading again for all records updated since time 2. Since record A was last changed at time 1, the reader won't read it. The changes to record A are never seen by the reader.

Try it.

In article <34C7A9E2.D64AD9E4_at_quebim.com>, MarkL_at_quebim.com wrote:

>The easiest thing to do would be to write a trigger to capture
>the date and time of the last change. In the simplest form:
>
>create or replace trigger check_changes_trg
>after insert or delete or update on YourTable
> Begin
> write the date and time;
> end;
>/
>
>
>M.Landa
>
>
>Åge Strand wrote:
>
>> [Newbie question]
>>
>> Hi!
>>
>> I need to be able to detect whether or not a table has changed since
>> the
>> last time I accessed it, so I can determine if I nedd to read it again
>>
>> or not. Some sort of time stamp denoting the last changes
>> (insert/delete/update...etc) to a table would be fine.
>>
>>
>> Please reply by mail to
>> Åge Strand
>> aage_at_ts.nera.no
 

-- 
Larry Reid                             lcreid@^web.net (remove the ^)