databaseoracle-databasetriggersdmlaudit-trail

Audit of DML transactions in a table

I want to record any DML transaction happens in a table in a day into another table. I also want to record transaction with values. How is it possible in Oracle Database?

I have been told not to use triggers because of its huge maintenance and also it slows application.

Solution

  • A couple of options you have would be

    1. Flashback Data Archive.

    This uses the flashback technology to capture changes of records which can then queried via the "versions between" syntax. Does not use triggers at all, but had some limitations. A video walkthrough of that here

    https://www.youtube.com/watch?v=qIs2UPIodQg

    1. Triggers for auditing can be efficient as long as they are written well (bulk bind etc). If you don't like the maintenance overhead, here is a tool that will automatically generate the triggers, as well as maintain them as the table changes over time.

    https://connor-mcdonald.com/2020/08/04/level-up-your-audit-trigger-game/