Re: audit sql queries

On Sun, 2007-09-09 at 23:13 +0000, Dan99 wrote:
> 1. table(s) affected
> 2. column(s) affected
> 3. action performed on data (ie. update, insert, select, delete)
> 4. previous data for each row and column effected (if data changed or
> deleted)
> 5. new data for each row and column effected (or existing data if data
> is being selected)

Auditing SELECTs is the trickiest; everything else can be done with
triggers.

For SELECT, you can use a view over a set-returning function, where the
function actually reads the underlying data, however that could be bad
for performance.

Alternatively, there is also kind of a trick where you can use an
uncorrelated subquery in a view so that the select has the side effect
of executing a function, like so:

CREATE VIEW mytable_audit AS SELECT * FROM mytable WHERE (SELECT
audit_func());

audit_func() should always return true, and should also record the other
information that you need. This strategy may perform better than using a
set-returning function.

Regards,
Jeff Davis