I'm looking for the better/best solution to compare data in a table before update and after. What is better solution - trigger and temp. table/global temp or materialized view (refreshed nightly) please? The Flashback query has its limitations - not considering this at this point.
The table is not big, less then 1000 rows. It may grow in the future, but not much. The whole idea is to compare today's last value of some indicator in that table with its today's initial value. For ex., init. value = N (morning). Then users update it to Y during the day. Then later it is updated to back to N (end of day). In this case I need to ignore this row. Otherwise, I need to update some other table based on the value of this indicator. The job to compare and update will run at night. Which solution is better please, trigger or m-view? Which is better for big tables, possibly with million(s) rows please?
Thank you Tim and all.