remembering which records were updated/inserted

[Jacob Galley]

Hello all. Any thoughts on the following problem?

On a table in the database I'm developing, I need to flag records that
are newly inserted or updated. The system I have put together for
this task works like this:

The table has two bit fields, f1 and f2. When a record inserted or
updated, a trigger sets f1=1. The flagged records are periodically
processed by a stored procedure, 'pc_aggregate'. Before this
procedure is executed, 'pc_wipe_flags' is called. This procedure sets
f2=f1, and f1=0. It's really just an update statement on all records
in the table. The idea here is that f2 stores the previous state of
the flags, because 'pc_aggregate' might continue processing while
another process is writing to the table, thereby raising more flags.

I would appreciate any comments on this implementation. This seems to
be a fairly general type of problem, and I bet some of you have
implemented a solution to it before. The issue that I am concerned
about is that the log will tend to fill up when 'pc_wipe_flags' is
executed repeatedly. I am expecting it to be called every minute or
more.

Is there a better way to implement this or should I just dump the log
every hour?

Thanks for any advice,
Jake.

[HYSW]

You can have a datetime field update_date in every record which gets set to
getdate() every time a record is inserted or updated. Each time your
processing routine runs, it records the time and stores this in a table as
last_time_run, the next time it runs, it only processes records with
update_date more recent than last run date.