I'm looking for the better/best solution to compare data in a table before update and after. This can be done with trigger and temp/global temp table or with read only m-view as far as I know. Would like to ask DBA community which solution is better, more efficient etc... The table is not big, less then 1000 rows. It may grow in the future, but not much. Which is better for big tables, with million(s) rows please? Thank you all.

  • 1
    How about nothing? Flashback query can do this for you. – Colin 't Hart Oct 1 '13 at 18:03
  • 2
    Why do you need to compare the before and after data, and what exactly are you planning to compare? – mustaccio Oct 1 '13 at 18:07
  • @Colin 't Hart - Thank you. Flashback may work. – Art Oct 1 '13 at 19:00
  • @mustaccio - There is some indicator in that table that user may update more then once per day. For ex., orig value = N, then updated to Y, then updated back to N. In this case I want to ignore this row and not to update some other table based on the value of that indicator... Thank you. – Art Oct 1 '13 at 19:03
  • I probably misunderstand something, but this looks to me as if the decision were made based on the final state of the indicator, so I don't see how comparing that to a previous state (by the way, which of the two?) would be needed. – mustaccio Oct 1 '13 at 23:35

If flashback does not work for you for some reason, you could add a column to your table to save the original value of the indicator and a trigger of the sort:

create trigger t
before update of ind_col on your_table
for each row
 if :old.ind_save_col = '' then 
   :new.ind_save_col := :old.ind_col; 
 end if;

(code not tested). Your nightly process would then copy rows where ind_col != ind_save_col, then reset ind_save_col.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.