> From: Paul Reeves [mailto:paul@...]

Hi,

> Yes, I often think that ability would make me happy, too. But I
> don't think this
> example is a good one.

It was just an example, but raises a point (see later)

> Ideally, this sort of auditing should be built
> into/extended from the existing engine. When a record is changed InterBase
> already knows what the differences are, and writes the old ones
> out as back record versions.

I am a bit undecided on this one, as my example showed, one mans audit trail
is different to another's. Those of us that have used Oracle, know that it
contains 'built' in auditing/logging functionality, but as it is pretty
generic, it never quite does what you want, and we normally end up
implementing it ourselves.

As for adding it to the engine, its another case of usefulness Vs bloat. If
it is added, it needs to be done properly, not just for 'another check in
the box'. Obviously we I don't know the 'inner bowels' of InterBase (yet),
but if it is possible just write the changes to an external text file by the
means of a system setting, this might be suitable for some situations.

> It has to be more efficient to add the ability add an option to
> write this out
> to disc/external file/whatever at that point than to create
> triggers on every
> table that simply repeat something the engine already knows.

Hands up for Global Triggers :-). If you could create a trigger that you
could 'hook up' to a number of tables, you would be able to write once, run
anywhere. This combined with DSQL, would save me so much time, I might be
able to take a holiday.

> I guess the questions that need to be answered are:
>
> o How important is an audit trail option?

I work in the financial sector, and virtually every change to every field in
every table needs to be stored, together with User, TimeStamp etc.
Normally, I have a category and importance level for each field in a
separate 'look-up' table, which allows different 'auditors' to review
different changes to a record, and also be alerted of any 'serious' changes.

We also have 'end-of-day'/'end-of-month' processes that reconcile the audit
logs with the 'live' system, to make sure they add-up.

Generally the 'audit' part of our system make up about 20-25% of the total
functionality, but as I said before, we are a 'heavy' user of 'audits', and
other people will only want a basic audit, or a just as 'complex', but
completely different audit.

> o Does dynamic SQL in SPs/Triggers have more utility
> than just supporting auditing?

We also use a similar method to my 'audit example' to create charges and
fees for our clients using the system. We charge our clients for 'database
activity' not a flat rate, so charges are generated when fields/records are
added/amended etc. Obviously we don't want to charge for all field changes
as some are for 'system' use, so again we use a look-up table to determine
which fields to charge for. Again DSQL and/or a Global Trigger would be
like winning the lottery.

> It is not that the two are mutually opposing - maybe both are
> needed. But if the
> aim is to implement an audit trail, is the DSQL option the right
> solution or
> just a symptom that the engine doesn't surface auditing yet?

I personally think that everyone's 'audit' requirements are different, so
building it into the engine might be difficult, and pointless, we don't want
to end up with the 'Audit' equivalent of the BDE. If it is simple to write
IB's versioning information out to an external file, then that should be OK,
but I think the ultimate solution is DSQL and 'Global Triggers', that way
people can do what they want, how they want.

Cheers

Phil Shrimpton
------------------------------
Project JEDI DCOM Team Captain
Project JEDI Library Team
<www.delphi-jedi.org>
Registered Linux User #155621