Oracle forms 10g

I have a form that has 6 data blocks and several items. When a user enters/queries an item, just before the user changes any item I would like to save the old values to a database table by using a trigger. Then I will later figure out how to save the changes to another table by using a trigger.
Tay JohnsonAsked:
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.

Pawan KumarDatabase ExpertCommented:
You can use Before Insert trigger..Sample code will be like below -

CREATE OR REPLACE TRIGGER yourTriggerName
BEFORE INSERT
   ON yourtableName
   FOR EACH ROW

BEGIN

    yourcodehere..;
    Insert into yourtableName...

END;

/

Open in new window

Tay JohnsonAuthor Commented:
I wanted to add an example:
Form:
block1.         Block2.      Block3.   Block4
Rec1..           Rec..           Rec..        Rec..
Item4.           Item5.       Item6.      Item7
AU.                 TY.             DS.            JH.


Result: item5 was changed to Gg
Tay JohnsonAuthor Commented:
Where would i place that trigger. At the block level
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Pawan KumarDatabase ExpertCommented:
You have database table in your database, So on that database table we have to create the trigger.
Tay JohnsonAuthor Commented:
Okay, that's a database trigger
Tay JohnsonAuthor Commented:
So when i move the cursor into the item prior to changing it. This database trigger will save the old values to the database.
Tay JohnsonAuthor Commented:
I am looking for a form trigger.
Mark GeerlingsDatabase AdministratorCommented:
Pawan: No database triggers are not what this question is asking for (but they may be helpful).

Tay: It is possible to do what you are asking for with Oracle Forms triggers.  But, I'm not sure that exactly what you described is the best way to get the job done in Oracle Forms.

I've often used a Post-Query trigger to save the old value into a non-displayed field in the block (not to a database table).  Are you sure that you want the old/current value saved to a database table as soon as the user starts to change a value, even if they never save their changes?
Mark GeerlingsDatabase AdministratorCommented:
I've often used a database trigger to save the "old" value for columns that we wanted to track changes to.  But, with this technique the change is not saved at the point in time you are asking for.  With this approach, the "old" value isn't saved to a history table until the new value gets saved.  I usually use a "sparse" history record format for this to save space, and to make the actual change very obvious when looking at the history records.  By a "sparse" history record, I mean save only the unique identifier of the record, plus only the "old" value(s) that was/were actually changed, plus the timestamp and user name.  Our history tables have a column structure identical to the original table, except in the history table, all of the columns except the unique identifier(s) and the timestamp and user name can be null.  I can post an example here of a database trigger that uses "nvl" on each column to decide whether the "old" value should be saved to the history record, or not, if you would like to see an example of that.

We don't know exactly what your business requirements are.  If you do really need to save a value to a database table when a user changes it in an Oracle Form, but before the user saves that change, you will need a combination of Forms triggers to do that, since there is no trigger event in Oracle Forms that corresponds exactly to a user starting to change a value in a form field (other than for the first field in the record that they start to change something in).  I would say that your question suggests an unusual business requirement, or at least an unusual way to do things in Oracle Forms.
Tay JohnsonAuthor Commented:
I am trying to create a table to see what were the previous values and what are the new values. Ex: itemOldValue-- aa, created_by tom, date 11/12/2001 itemNewValue-- bb, created_by peter, date 11/21/2017
Mark GeerlingsDatabase AdministratorCommented:
There are basically two different options for creating history/audit tables to track changes to column values:

Option one: create a generic history table with a column structure something like this:
1. schema_name
2. table_name
3. column_name
4. old_value
5. new_value
6. timestamp
7 user_name

Option two:
Create a history table for each table that you want to capture changes for.  Each history table should have columns exactly like the original table (plus timestamp and user_name columns if the original table doesn't include them) with the difference that most columns in the history table can be null.

I prefer option two.  What you described looks like my option one.  Either option can work.  But either way, creating the history records is much easier in Oracle Forms if you create them at commit time, instead of "just before the user changes any item" as you described in your question.
Mark GeerlingsDatabase AdministratorCommented:
Here are the (partial) column lists for the item_master and corresponding item_master_history table in our system, plus part of the trigger we use that captures the "old" values for any column(s) that get changed.  The "new" values are visible in the base table, so we don't take space in the history table to copy the new value(s) there.  If the same column(s) get(s) changed again later, the trigger again captures the "old" value(s) at that point in time.  This trigger was written some years ago so it uses the Oracle-native "decode" syntax instead of the newer, ANSI-standard "case" syntax to determine whether a particular column value should be saved to our history table, or not.

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 PART_NBR                                  NOT NULL VARCHAR2(15)
 PART_DESC                                 NOT NULL VARCHAR2(60)
 PART_TYPE                                 NOT NULL VARCHAR2(10)
 PRODUCT_CAT                               NOT NULL VARCHAR2(2)
 INVENTORY_UNIT                                     VARCHAR2(2)
 PURCHASING_UNIT                                    VARCHAR2(2)
 PUR_INV_MULTIPLIER                                 NUMBER(16,8)
 OBSOLETE_DATE                                      DATE

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 PART_NBR                                  NOT NULL VARCHAR2(15)
 DATE_MODIFIED                             NOT NULL DATE
 MODIFIED_BY                               NOT NULL VARCHAR2(15)
 PART_DESC                                          VARCHAR2(60)
 PART_TYPE                                          VARCHAR2(10)
 PRODUCT_CAT                                        VARCHAR2(2)
 INVENTORY_UNIT                                     VARCHAR2(2)
 PURCHASING_UNIT                                    VARCHAR2(2)
 PUR_INV_MULTIPLIER                                 NUMBER(16,8)
 OBSOLETE_DATE                                      DATE
 
 
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/1900'
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, ...)
values(:new.part_nbr, sysdate, 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/1900','DD/MM/YYYY'))),
  ...);

Open in new window

Tay JohnsonAuthor Commented:
I wii create a database trigger from the example and get back to you tomorrow. Thanks
Tay JohnsonAuthor Commented:
We have been having system problems all day.
Tay JohnsonAuthor Commented:
I am unable to use that trigger because i have to join two tables. Is there another way
Mark GeerlingsDatabase AdministratorCommented:
One option would be to create a history table for each of your two tables and have two triggers, one for each table, to save the actual values that are changed in each table.  That would be the simplest way to have the database help you save the changed values.

If you don't want to (or can't) use that option for some reason, you will have to decide exactly where and how you want these values to be saved.  I don't know exactly which values you want saved, where you want them to be saved to, or exactly when you want them to be saved.

So, I don't know if database table triggers would be best, or possibly a database "instead of" trigger on a view that joins the two tables would be best, or if you need to do this with Oracle Forms triggers.  In theory, any of these options could do the job for you.
Tay JohnsonAuthor Commented:
there serveral data blocks tie to one form and the users are changing the field values. I'm trying to find out what the old values were and new values. Within the history table i want to list date_created, created_by, modified_by, date_modified, old_item_name, new_item_name, ..... I'm not sure if a database trigger can do it.  I have to think some more about this. I really do appreciate you get back to me so quickly.
Tay JohnsonAuthor Commented:
I'll do some more digging.
Tay JohnsonAuthor Commented:
I do want all the values saved to the database.
slightwv (䄆 Netminder) Commented:
What is the database version and edition?

You can always try to create your own product to do what you want but I try to use what is already written.

Have you considered what is available with Auditing?  If you are running Enterprise Edition you might be able to get what you want with Fine Grained Auditing (FGA).

Maybe even Change Data Capture.  

I'll let you read up on them depending on the database version you have.
Tay JohnsonAuthor Commented:
Oracle 12c
Tay JohnsonAuthor Commented:
Oracle 12c EE
Tay JohnsonAuthor Commented:
Oracle developer suite 10g
slightwv (䄆 Netminder) Commented:
I would definitely look at FGA to see if it will meet your requirements.
Tay JohnsonAuthor Commented:
I never used FGA before. I will review the documentation to see if it is a fit then try an set it up. I'll let you know if it works.
Mark GeerlingsDatabase AdministratorCommented:
I admit that I haven’t explored Fine Grained Auditing.  The older Oracle audit option captured way too much information in my opinion (host_name, IP_address and other details) but not the actual values changed.  Maybe FGA can capture the values being changed without too much other clutter.
Custom database triggers that you write can certainly reliably capture exactly and only the details you want, both the “before” and “after” values if you want, in a single trigger.
Tay JohnsonAuthor Commented:
The problem with the database trigger is i can't use a view. And i need it to fire after an update.
Tay JohnsonAuthor Commented:
I not sure FGA will work but I'll continue to figure it out. Maybe I can use a form trigger. I'll keep digging.
slightwv (䄆 Netminder) Commented:
>>The problem with the database trigger is i can't use a view

You can create triggers on views.  They are called "instead of" triggers:
https://docs.oracle.com/database/122/LNPLS/plsql-triggers.htm#GUID-9F06D45C-7C60-434E-A597-114A0C445671
Mark GeerlingsDatabase AdministratorCommented:
1. The quickest option: write a "before update ..for each row" or an "after update ...for each row" trigger on your two tables.  Either of these can capture exactly what you want captured (including: the "before"' or "after" value of each column, or both) and save this where ever you want it to be saved.

2. A second option  (assuming that your Oracle Forms block is based on a join view): write an "instead of update" trigger on the view to capture exactly what you want captured and save it where ever you want it saved.  If you use this option, be careful!  Remember that your trigger also has to update the underlying tables!

3. A third option: use a combination of Oracle Forms triggers (post-query plus post-update) to capture exactly what you want captured and save it where ever you want it saved.

4. A fourth (and/or fifth) option: explore Fine Grained Auditing and Change Data Capture to see if they can capture what you want captured (and don't add too much complexity or cost or too much other data that you don't care about).

Which of these options is best for you?  That is hard for us to say.  I know that any of the first three options can work. I've used all of them.  I assume that FGA or CDC can also work, but I haven't used them.

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
slightwv (䄆 Netminder) Commented:
>>I assume that FGA or CDC can also work, but I haven't used them.

That is also my assumption.  I've not done much with either of them. I just knew about them and thought they might be alternatives.
Tay JohnsonAuthor Commented:
I will try explore all options listed below to find out which one produces the results i want with the best performance and get back to you.
Tay JohnsonAuthor Commented:
For the second option, Instead of Update trigger i don't want to update the underlyinging tables. So let's look at the form triggers. I can create two procedures, one to get historical data and another procedure to new data an place it in the same table. But where how and where in the form should I invoke them.
slightwv (䄆 Netminder) Commented:
>>Instead of Update trigger i don't want to update the underlyinging tables.

OK, then don't.  In a trigger you can update/insert/whatever.  It doesn't need to be the base tables.  You just need the triggering event on the base table.

I cannot help with anything Forms related so you'll have to wait for Mark.
Mark GeerlingsDatabase AdministratorCommented:
The underlying tables are getting updated one way or another, so that's why it sounds best to me to use database triggers either on the base tables or on the view.

But, if you want to do this in Oracle Forms, you need a minimum of two Oracle Forms triggers:
1. A Post-Query trigger to save the current values to either:
  a. non-displayed, non-base-table items that you add to the current block (my recommendation)
  b. non-base table items in a non-base table "control" block in the form
  c. a PL\SQL record
2. a Post-Update trigger to retrieve the values from wherever your post-query trigger put them, and insert them where ever you want them to be saved.
Tay JohnsonAuthor Commented:
Okay. I'm going to try the form triggers.
Tay JohnsonAuthor Commented:
I used a form trigger but I am not sure if it is working because I am receiving an ORA-00001 unique constraint error so the table will not load.
Mark GeerlingsDatabase AdministratorCommented:
The ORA-00001 error will include the name of the constraint.  Query the dba_constraints (or user_constraints) view to see which table that is.  That is the table where the database detected an attempt to insert a duplicate record, and stopped you with the ORA-00001 error.

That doesn't mean that using a form trigger cannot work.  It just must be written so it does not create duplicate records.

(No, this doesn't mean that I think form triggers are better than database triggers for your issue.  But it sounds like you want to only use forms triggers.)
Tay JohnsonAuthor Commented:
Thank you
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
Query Syntax

From novice to tech pro — start learning today.