I'm just  starting on the triggers for monitoring changes to key tables in the app. Since its not intended to provide rollback/rollforward capabilities my current thoughts are table, time and user details and a CLOB column holding

Field: oldvalue - newvalue

Any comments?

Roy Lambert

Sounds reasonable but depends on what you wish to use it in long term.

What i've done in the past it bit more wasteful but did it as follows:
- stored the field name, old and new values as separate fields
- also stored the table unique record id so that old and new value are
matched to a proper row in table

In my case this made things a whole lot easier (sql and code side) at the
expense of additional processing time and storage space (something i was
more than willing to sacrifice as it was unnoticable). Everything was
handled at the field level (so no in-field parsing and worrying about
escaping field content if it contains the delimiter). We did end up
implmenting a fairly cool rollback since all the data is there so we could
rollback a specific client record and such.

Raul




"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:9FF5E770-76DF-4610-9FF1-3166A47FB2AD@news.elevatesoft.com...
> I'm just starting on the triggers for monitoring changes to key tables in
> the app. Since its not intended to provide rollback/rollforward
> capabilities my current thoughts are table, time and user details and a
> CLOB column holding
>
> Field: oldvalue - newvalue
>
> Any comments?
>
> Roy Lambert
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 4138 (20090608) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>



__________ Information from ESET NOD32 Antivirus, version of virus signature database 4138 (20090608) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

Roy,

<< I'm just starting on the triggers for monitoring changes to key tables in
the app. Since its not intended to provide rollback/rollforward capabilities
my current thoughts are table, time and user details and a CLOB column
holding >>

What I should do is try to surface the update tracking for the replication.
Then you could just publish a table and query a special system table to see
the changes done to a specific table or tables.

--
Tim Young
Elevate Software
www.elevatesoft.com

Raul


>What i've done in the past it bit more wasteful but did it as follows:
>- stored the field name, old and new values as separate fields
>- also stored the table unique record id so that old and new value are
>matched to a proper row in table

Strange you should say that After posting I decided I'd have a row per field altered.

Roy Lambert

Tim

>What I should do is try to surface the update tracking for the replication.
>Then you could just publish a table and query a special system table to see
>the changes done to a specific table or tables.

That sounds interesting, but I'd need a few more clues. "surface the update tracking " duh?

Roy Lambert

Roy,

<< That sounds interesting, but I'd need a few more clues. "surface the
update tracking " duh? >>

When you publish a database/tables for replication, the table begins to keep
track of all inserts, updates, and deletes to itself.  If this information
was somehow surfaced, one could use it for auditing a database/tables
instead of replication, and any time one wants to clear out the auditing,
they could just execute a SAVE UPDATES operation.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I'll have a look as see if I can do something with it. If not I have my trigger for the first table.

Roy Lambert

I just wrote an extension to the DBISAM server (no ElevateDB, sorry) that does just that.
It can protocol every changes in a table, with user name and IP address (ok, not 100%
automatically because like many more I use the same DB user for all instances, so the
client needs about 10 lines of code to write some data in a in-memory table), old value
(excelt BLOBs) etc etc. It also can add Created and Modified information automatically to
each record of existing tables, you just have to define some fields. Everything is 100%
back and forth compatible - the modified server runs with standard clients, and the
unmodified server runs with modified clients.

Just write me an email if anybody is interested.