"ORA-01779: cannot modify a column which maps to a non key-preserved table"

I don't have experience for instead-of-trigger. Can you tell me how to do it? I provide the view above. How to modify my view to suit as instead-of-trigger.

"If if forms is used an on-update-trigger on the block you have" how to do that in this approach

well,

you have first to include your keyfields into your fieldlist of your view

in your case it would be
upgrade_input_id for your mastertable

and i guess there will be an additonal field in your detail-table (maybe position)
or maybe it is already in the your fieldlist

could be this inclusion could be enough for oracle to handle updates without additional action (depends on the version),

if not, the you could attach an instead-of-trigger to your view

a sceleton:

CREATE OR REPLACE TRIGGER your_schema.your_trigger_name
  INSTEAD OF INSERT OR UPDATE OR DELETE  --for all cases of dml
  ON your_schema.your_view_name   FOR EACH ROW
BEGIN
   -- you can only insert / update values, which are part from the view,
   -- all others, which are not included in the view, may have constant-values,
   -- or must be calculated/retrieved before on another way
  IF INSERTING THEN
   --maybe any handling for getting a sequence-value before
    INSERT INTO MasterTable (Field1, Field2, . . .) VALUES (:new.mField1, :new.mField2,....);
    INSERT INTO DetailTable (Field1, Field2, . . .) VALUES (:new.dField1, :new.dField2,....);
  END IF;
  IF UPDATING THEN
    UPDATE  masterTable set Field1 = :new.mField1, .... WHERE keyfield = :old.mKeyField;
    UPDATE  detailTable set Field1 = :new.dField1, .... WHERE keyfield1 = :old.dKeyField1 and keyfield2 = :old.dKeyField2;
  END IF;
  IF DELETING THEN
    --Detail First
    DELETE FROM detailTable WHERE keyfield1 = :old.dKeyField1 and keyfield2 = :old.dKeyField2;
    DELETE FROM masterTable WHERE keyfield = :old.mKeyField;
  END IF
END;

well, just a sceleton from head, the content you should adjust to your needs

meikl ;-)

After reading your code, it's above my head. I think I prefer to do it by on-update trigger method. Do you show me detail about it. I think I can pick up since I have a little experience about it.

Ok, I write the following code on "On-update" trigger but I cannot create the form with following error

Compiling ON-UPDATE trigger on MEDIA_REQUEST_INFO block...
Compilation error on ON-UPDATE trigger on MEDIA_REQUEST_INFO block:
PL/SQL ERROR 103 at line 3, column 1
Encountered the symbol "PROCEDURE" when expecting one of the following:
 "begin" "declare" "exit" "for" "goto" "if" "loop" "mod"
    "null" "raise" "select" "update" "while" <an identifier>
    <a double-quoted string> <a bind variable>
    <a single-quoted SQL string> "<<" "close" "delete" "fetch"
    "lock" "insert" etc.
declare was inserted before "PROCEDURE" to continue.

PL/SQL ERROR 49 at line 9, column 14
bad bind variable 'PARAMETER.program_id'
PL/SQL ERROR 103 at line 2, column 1
Encountered the symbol "END" when expecting one of the following:
 "begin"
Resuming parse at line 44, column 4.


can anyone fix for me? Also, do I need "On-insert" trigger too?

PROCEDURE Update_Row IS
v_prog_id NUMBER;
v_user_id NUMBER;
 
  BEGIN
 
v_prog_id := :PARAMETER.program_id;
v_user_id := FND_PROFILE.Value('USER_ID');
 
UPDATE xx_upgrade_inputs xui
SET
 
xui.license_number  = :media_request_info.license_number
,xui.license_id  = :media_request_info.license_id
,xui.source =:media_request_info.source
 
WHERE xui.upgrade_input_id=:media_request_info.upgrade_input_id
and xui.action = 'SHIP'
and xui.status IN ('IN_PROGRESS','REQUESTED','CANCELED');
 
 
UPDATE xx_upgrade_input_details xuid 
set
 
xuid.inventory_item_id = :media_request_info.inventory_item_id
,xuid.licensed_products_id=:media_request_info.licensed_products_id
,xuid.status=:media_request_info.status
,xuid.s1_date =:media_request_info.requested_date
,xuid.s3_date =:media_request_info.in_progress_date
,xuid.s6_date =:media_request_info.completed_canceled_date
,xuid.ship_batch_id =:media_request_info.batch_number
 
where 
xuid.upgrade_input_id = :media_request_info.upgrade_input_id;
 
 
END Update_Row;
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:

Please, post here your ON_UPDATE trigger.

I posted it above. I post again below

PROCEDURE Update_Row IS
v_prog_id NUMBER;
v_user_id NUMBER;
 
  BEGIN
 
v_prog_id := :PARAMETER.program_id;
v_user_id := FND_PROFILE.Value('USER_ID');
 
UPDATE xx_upgrade_inputs xui
SET
 
xui.license_number  = :media_request_info.license_number
,xui.license_id  = :media_request_info.license_id
,xui.source =:media_request_info.source
 
WHERE xui.upgrade_input_id=:media_request_info.upgrade_input_id
and xui.action = 'SHIP'
and xui.status IN ('IN_PROGRESS','REQUESTED','CANCELED');
 
 
UPDATE xx_upgrade_input_details xuid 
set
 
xuid.inventory_item_id = :media_request_info.inventory_item_id
,xuid.licensed_products_id=:media_request_info.licensed_products_id
,xuid.status=:media_request_info.status
,xuid.s1_date =:media_request_info.requested_date
,xuid.s3_date =:media_request_info.in_progress_date
,xuid.s6_date =:media_request_info.completed_canceled_date
,xuid.ship_batch_id =:media_request_info.batch_number
 
where 
xuid.upgrade_input_id = :media_request_info.upgrade_input_id;
 
 
END Update_Row;

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:

any ideas?

The code you have posted ougth to be in a Program Units section of your form.
Then the code of your On-Update trigger will look like this:

Update_Row;

I don't want to use program unit, I want the code directly dump into On-update trigger. Is it possible? If not, I have to use program unit.

In program unit, I create a procedure called update_row, right? and dump my code into the update_row procedure and call the update_row from update trigger, right?

It will serve the same purpose?

If you want to use your code directly in On-Update trigger, then it ought to look like this:

DECLARE
v_prog_id NUMBER;
v_user_id NUMBER;
BEGIN
 
v_prog_id := :PARAMETER.program_id;
v_user_id := FND_PROFILE.Value('USER_ID');
 
UPDATE xx_upgrade_inputs xui
SET
 
xui.license_number  = :media_request_info.license_number
,xui.license_id  = :media_request_info.license_id
,xui.source =:media_request_info.source
 
WHERE xui.upgrade_input_id=:media_request_info.upgrade_input_id
and xui.action = 'SHIP'
and xui.status IN ('IN_PROGRESS','REQUESTED','CANCELED');
 
 
UPDATE xx_upgrade_input_details xuid
set
 
xuid.inventory_item_id = :media_request_info.inventory_item_id
,xuid.licensed_products_id=:media_request_info.licensed_products_id
,xuid.status=:media_request_info.status
,xuid.s1_date =:media_request_info.requested_date
,xuid.s3_date =:media_request_info.in_progress_date
,xuid.s6_date =:media_request_info.completed_canceled_date
,xuid.ship_batch_id =:media_request_info.batch_number
 
where
xuid.upgrade_input_id = :media_request_info.upgrade_input_id;
END;

"In program unit,.....It will serve the same purpose?" - yes.

well, sorry for delay, was absent.

Henka already told all

just replace

PROCEDURE Update_Row IS

with

DECLARE

there may also a problem with parameter :PARAMETER.program_id
(maybe it does not exist)

meikl ;-)

Hey, it seems working. But, when I want to save it, I don't see a message saying "....saved" in the tool bar. But it did save
The message popped up when I close the form.

How to let the message popped up in the tool bar when I click "action->save"

second, do I need to put a line "commit;" in each update statement above??

If you use default menu then commit; is there and there is no need to change it.

You can also create your own "save button" on the form and put commit; in its's when-button-pressed trigger.
The code of this trigger can look like this:

commit;
message('Changes has been saved.');

Your recommendation is good. I see the message. Now I comes up another error message after I modify the view and update trigger. I add two tables to the view. When I want to edit a field in the form, it pops up

FRM-40501: ORACLE error: unable to reserve record for update or delete
ORA-01446:cannot select ROWID from view with Distinct, group by, etc

When I remove the distinct in my view's select statement. I compile again. But still doesn't work. It comes up the message below

FRM-40501: ORACLE error: unable to reserve record for update or delete
ORA-01445:cannot select ROWID from a join view without a key-preserved table.

I attached my view and update trigger for you to take a look Can you tell me how to modify my update trigger.

Sending me with attachment will be the best.

I increase the points to 200. I guess this is not easy to solve and I add more requests

I thik that the problem is caused by Key Mode Block property. By default it is set to Automatic and help says:
Specifies that Oracle Forms should use ROWID constructs to identify unique rows in the datasource but only if the datasource supports ROWID.

I think that you ought to use Non-Updateable or Updateable.
Plese, read "Key Mode Property" topic in the on-line help.

Ok, I changed to Updateable

But, unable to creat a form with following two errors

FRM-30050: Block with uniqueness check must have at least one key item.
Block: MEDIA_REQUEST_INFO
Form: XXLICN
FRM-30085: Unable to adjust form for output.
Form not created

How to fix?

In your block you ought to set Primary Key property of item (which correspond to primary key) to yes.

no no,

you must lock yourself, or even decide self to not lock

just provide a code in the On-Lock-Trigger of the Block, like

in case of no locking, place simple

NULL;

here

in case you want to lock the associated rows of your joined tables just place a code like

declare
  keyofmaster number;
  keyofdetail number;
begin
  select keyfieldfrommaster into keyofmaster from mastertable where [yourCondition] for update nowait;
  select keyfieldfromdetail into keyofdetail from detailtable where [yourCondition] for update nowait;
end;

the locks are resolved by commit or rollback

hope this helps

meikl ;-)

Hi Henka: I set the primary key to true already. it's still the same problem.

kretzschmar:
I created a trigger on-lock in my block medi_request_info and put

null;

I complied it and still the same problem.

For the code you provided above
declare
  keyofmaster number;
  keyofdetail number;
begin
  select keyfieldfrommaster into keyofmaster from mastertable where [yourCondition] for update nowait;
  select keyfieldfromdetail into keyofdetail from detailtable where [yourCondition] for update nowait;
end;

Do I need that in on-lock trigger? How to put it based on my attachment?

If I have on-lock trigger, do I need on-update trigger as well?

I am not sure but it seems to me that you can update only one table of view.

It sounds that we can update multiple tables for a view, right?

i have no problem to update mutiple tables from a view, using
- an instead of trigger
or
- an on-update/on-Lock-trigger

well, usual on-lock should work, but my knowledge is from the 6i edition.

what version do you use?

meikl ;-)

I am using 8i database and form version is 11.0.35. I have on-update trigger attached above and you can download and have a look.

I don't understand if you mean I need on-dated trigger also need on-lock trigger, or mean either...or...?