Maintain change in Oracle table

Hello , in one of my oracle databases, I have one table name account,   I designed a form for users to display and edit the data , How I record all changes happened to that table via that form ? In other word , I want a record of the original data and the changes happened, note that there are 15 fields for that table able to change
NiceMan331Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
There are multiple options.  Basically you can use either Oracle Forms triggers (post-query plus pre-update) or a database trigger (after update ... for each row).

If you are confident that no user will ever use anything other than your Oracle Form to change this data, then you can use Oracle Forms triggers to capture the changes.  To be safe, and make sure the changes get captured if someone ever uses SQL Developer or TOAD or some other tool to update the data directly, then a database trigger is more reliable.

You also have to decide what you want your history table to look like.  There are basically two options for this:
1. A generic audit table with these columns:
  table_name
  column_name
  old_value
  new_value (optional)
  time_stamp
  username
2. A "sparse" history table.  This table has mostly the same column structure as your account table, but with these two differences:
 - all columns except the primary or unique key column(s) are allowed to be null
 - if your "accounts" table does not include columns like these two (modified_date and modified_by or username) then add those two columns to the history table.

I like option #2 better, but either of these options can work for capturing history.

If you use Oracle Forms triggers, you will need to add a non-displayed, non-database field in your "accounts" block for each field in the "accounts" record that the user can change.  You need a post-query trigger in the form to copy the current value of each database field to its corresponding non-displayed, non-database field, so you have the previous value available when the user saves a change.  Then your pre-update trigger in the block needs to compare each database field value to its corresponding non-displayed, non-database field value, to see if they are the same, or different.  If different, you need to save the old value to your audit or history table.  (You don't need to save the new value to history, since that is still available in the accounts record itself, until someone changes it again, but then that change will be captured.)

I'll post a copy of a database trigger that we used in one of our systems here to populate a "sparse" history table with just the column values that were actually changed (plus the time_stamp and username).  The advantage of this option for history is that the history records are small, and when you look at them you can easily see the old values in any columns that were changed, and when and by whom.  A disadvantage is: you do have to look at later history records, or the current record to see what the values were changed to.
 
create or replace trigger item_master_au_row
after update on item_master
for each row
begin
-- This saves a "sparse" history record for each item_master update.
-- Three fields: part_nbr, date_modifed, and modified_by are saved in all cases,
--  plus only the field(s) changed.  If the field being changed was null,
--  the following nvl's are applied:
--   varchar2 fields = '?'
--   number fields = 0
--   date field = '01/01/2099'
if :new.part_nbr <> :old.part_nbr then
  raise_application_error(-20201,'Changing the part nbr. is not allowed');
end if;
if :new.created_by <> :old.created_by then
  raise_application_error(-20202,'Changing the "created_by" is not allowed');
end if;
if :new.date_created <> :old.date_created then
  raise_application_error(-20203,'Changing the "date_created" is not allowed');
end if;
if :new.date_modified is null then
  raise_application_error(-20204,'Program error: "Date_modified" may not be blank');
end if;
insert into item_master_history
 (part_nbr, date_modified, modified_by,
  part_desc, part_type, product_cat,
  inventory_unit, purchasing_unit, pur_inv_multiplier,
  obsolete_date, activity_flag, pur_manu_flag,
  planning_lead_time, chemical_flag, acct_nbr,
  finished_good_flag, plan_order_multiplier, stock_req_man_qty,
  stock_req_sug_qty, std_pack_qty, current_approved_revision, 
  current_engineering_revision, current_engineering_release,
  extra_desc, price_decimal, supplier_code,
  buyer, notes, default_bin, 
  comment1, comment2, tariff_code,
  forecast_flag, reorder_level, reorder_qty,
  country_of_origin_code, purchasing_kanban_flag, part_category,
  part_subcategory, drawing_nbr, drawing_revision)
values(:new.part_nbr, sysdate, user_pkg.show_user,
  decode(:new.part_desc,:old.part_desc,null,:old.part_desc),
  decode(:new.part_type,:old.part_type,null,:old.part_type),
  decode(:new.product_cat,:old.product_cat,null,:old.product_cat),
  decode(:new.inventory_unit,:old.inventory_unit,null,nvl(:old.inventory_unit,'?')),
  decode(:new.purchasing_unit,:old.purchasing_unit,null,nvl(:old.purchasing_unit,'?')),
  decode(:new.pur_inv_multiplier,:old.pur_inv_multiplier,null,nvl(:old.pur_inv_multiplier,0)),
  decode(:new.obsolete_date,:old.obsolete_date,null,nvl(:old.obsolete_date,to_date('01/01/2099','DD/MM/YYYY'))),
  decode(:new.activity_flag,:old.activity_flag,null,:old.activity_flag),
  decode(:new.pur_manu_flag,:old.pur_manu_flag,null,:old.pur_manu_flag),
  decode(:new.planning_lead_time,:old.planning_lead_time,null,nvl(:old.planning_lead_time,0)),
  decode(:new.chemical_flag,:old.chemical_flag,null,:old.chemical_flag),
  decode(:new.acct_nbr,:old.acct_nbr,null,nvl(:old.acct_nbr,'?')),
  decode(:new.finished_good_flag,:old.finished_good_flag,null,:old.finished_good_flag),
  decode(:new.plan_order_multiplier,:old.plan_order_multiplier,null,nvl(:old.plan_order_multiplier,0)),
  decode(:new.stock_req_man_qty,:old.stock_req_man_qty,null,nvl(:old.stock_req_man_qty,0)),
  decode(:new.stock_req_sug_qty,:old.stock_req_sug_qty,null,nvl(:old.stock_req_sug_qty,0)),
  decode(:new.std_pack_qty,:old.std_pack_qty,null,nvl(:old.std_pack_qty,0)),
  decode(:new.current_approved_revision,:old.current_approved_revision,null,nvl(:old.current_approved_revision,'?')),
  decode(:new.current_engineering_revision,:old.current_engineering_revision,null,nvl(:old.current_engineering_revision,'?')),
  decode(:new.current_engineering_release,:old.current_engineering_release,null,nvl(:old.current_engineering_release,'?')),
  decode(:new.extra_desc,:old.extra_desc,null,nvl(:old.extra_desc,'?')),
  decode(:new.price_decimal,:old.price_decimal,null,nvl(:old.price_decimal,0)),
  decode(:new.supplier_code,:old.supplier_code,null,nvl(:old.supplier_code,'?')),
  decode(:new.buyer,:old.buyer,null,nvl(:old.buyer,'?')),
  decode(:new.notes,:old.notes,null,nvl(:old.notes,'?')),
  decode(:new.default_bin,:old.default_bin,null,nvl(:old.default_bin,'?')),
  decode(:new.comment1,:old.comment1,null,nvl(:old.comment1,'?')),
  decode(:new.comment2,:old.comment2,null,nvl(:old.comment2,'?')),
  decode(:new.tariff_code,:old.tariff_code,null,nvl(:old.tariff_code,'?')),
  decode(:new.forecast_flag,:old.forecast_flag,null,nvl(:old.forecast_flag,'?')),
  decode(:new.reorder_level,:old.reorder_level,null,nvl(:old.reorder_level,0)),
  decode(:new.reorder_qty,:old.reorder_qty,null,nvl(:old.reorder_qty,0)),
  decode(:new.country_of_origin_code,:old.country_of_origin_code,null,nvl(:old.country_of_origin_code,'?')),
  decode(:new.purchasing_kanban_flag,:old.purchasing_kanban_flag,null,nvl(:old.purchasing_kanban_flag,'?')),
  decode(:new.part_category,:old.part_category,null,nvl(:old.part_category,'?')),
  decode(:new.part_subcategory,:old.part_subcategory,null,nvl(:old.part_subcategory,'?')),
  decode(:new.drawing_nbr,:old.drawing_nbr,null,nvl(:old.drawing_nbr,'?')),
  decode(:new.drawing_revision:old.drawing_revision,null,nvl(:old.drawing_revision,'?')));
--exception
--  when others then
--    display(:new.part_nbr||' - error');
end;
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
if you ddidn't maintain the history from the start, and haven't set auditing on for that table changes,
then you can't reproduce the historical changes

there is a quicker way to setup such a history table

create materialized view log on yourschema.yourtable ... ;
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-MATERIALIZED-VIEW-LOG.html#GUID-13902019-D044-4B79-9EB4-1F60652D037B

this is part of a system to maintain a materialized view
but it can be used for historic record keeping
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.