-2

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.

  • 1
    This is the same as your other question: https://stackoverflow.com/questions/63438050/storing-daily-data-into-another-table – EdStevens Aug 16 '20 at 18:58
  • yes..because I am not getting answer so asking again and again. please dont downvote –  Aug 16 '20 at 19:08
  • @EdStevens Can you answer either of them? –  Aug 16 '20 at 19:10
  • 1
    Try such a format : `AUDIT INSERT, UPDATE, DELETE ON tab BY ACCESS;` if you have DBA privileges. – Barbaros Özhan Aug 16 '20 at 20:02
  • 1
    @BarbarosÖzhan Thank you Barbaros. Actually the purpose is to store all the DML with values in another table day wise without doing any partition. Will aboe solution workl –  Aug 16 '20 at 20:27
  • @BarbarosÖzhan this is the actual question for which I am looking for answer https://stackoverflow.com/questions/63427197/sending-incremental-data-to-other-application-from-oracle-database-even-small –  Aug 16 '20 at 20:27
  • 3
    You're going to get downvoted if you flout community practice by asking the same question multiple times. That's just the way it is. Basically, Oracle has built-in audit capabilities. [Read the documentation](https://docs.oracle.com/database/121/DBSEG/part_6.htm#DBSEG006) and decide which option fits your scenario best. If you don't like any of the built-in features triggers are your only alternative. But probably you should be able to use Oracle's built-ins (which also use triggers under the hood). – APC Aug 16 '20 at 21:06
  • 1
    Thank you @APC . i will read the link provided by you. Also I will delete other questions. –  Aug 16 '20 at 21:11
  • 1
    Sure. If you have a specific question about an aspect of Oracle's auditing capability by all means ask it – APC Aug 16 '20 at 21:19
  • 1
    "yes..because I am not getting answer so asking again and again". Sorry, that's not it works. Asking the same question again and again just pi**** people off. Not to mention that this is a global forum and the guy with the answer may live 12 time zones away from you and so not even see your question for several until the next day. – EdStevens Aug 16 '20 at 21:25
  • @EdStevens Please forgive me once. don't downvote. they will block my account. my first account was blocked same day when I created. –  Aug 17 '20 at 14:11

1 Answers1

0

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/

Connor McDonald
  • 10,418
  • 1
  • 11
  • 16