1

Background

Various databases have custom implementations for auditing:

Problem

A number of problems exist:

  • There is no consistency (different commands, different log formats, etc.)
  • There does not appear to be an ANSI SQL standard
  • Some of the audits log to files, rather than a table or tables

Question

What ANSI SQL table auditing mechanism is:

  • database agnostic; and
  • tracks changes that can be queried using SQL (i.e., an audit table, not a log file)?

If no ANSI SQL standard for audit tables exists, then what alternatives exist for database-agnostic auditing (e.g., create a custom audit table) of CRUD operations against all tables in a schema?

Community
  • 1
Dave Jarvis
  • 833
  • 1
  • 10
  • 25

1 Answers1

2

Triggers make an auditing mechanism, that satisfies your criteria.

  • they are present in the ISO/ANSI standard (ISO 9075)
  • they can be used to track row-level changes
  • they can save data to tables, hence can be queried using SQL

Only thing I do not understand in your question is "database agnostic" - what does it mean, precisely?

filiprem
  • 6,747
  • 1
  • 19
  • 32