| frm 40657 error [message #122569] |
Tue, 07 June 2005 05:59  |
harmeet.gambhir
Messages: 30
Registered: June 2005
|
Member |
|
|
i have created a data block based on a view based on four tables and have created a instead of update trigger on the view. i'm pasting the code for instead of update trigger and select query for the view in the data block.
i'm using oracle 9i database and 9i developer suite.
view definition is:
CREATE OR REPLACE VIEW CHALLAN_ITEM_DETAILS_V
AS select a.vendor_id,a.challan_id,b.challan_item_id,b.item_id,c.item_name,
b.item_qty,b.uom_id,d.uom_name,b.item_rate,b.item_status from
challan_details a,challan_item_details b,item_details c,uom_details d
where a.challan_id=b.challan_id and b.item_status='U'
and b.item_id = c.item_id
and b.uom_id=d.uom_id
instead of trigger definition:
create or replace trigger challan_item_ip_trig_2
instead of update on challan_item_details_v
for each row
declare
mrn_nu number;
begin
select mrn_seq.currval into mrn_nu from dual;
if :new.item_status = 'Y' then
insert into mrn_ch_details(vendor_id,mrn_id,challan_id,item_id,qty_received,
qty_accepted,uom_id,item_rate) values(:old.vendor_id,mrn_nu,:old.challan_id,:old.item_id,
:old.item_qty,:old.item_qty,:old.uom_id,:new.item_rate);
update challan_item_details set item_rate=:new.item_rate,item_status=:new.item_status
where challan_item_id=:old.challan_item_id;
end if;
end;
i have made item_status field as checkbox.
only item_rate and item_status fields are enabled rest all fields are display fields..
what happens is that first time the form is committed ok but if try to update the item_rate field again by modifying its value.
i get the error:
frm - 40657 record changed or deleted by another user.
i will be extremely grateful to everybody if anybody could help me out.
thanks in advance
|
|
|
|
| Re: frm 40657 error [message #122667 is a reply to message #122569] |
Tue, 07 June 2005 21:29  |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Sounds like a self-mutating trigger to me.
Have a look at USER_UPDATABLE_COLUMNS to see what the system thinks of your view.
Search the Oracle doco or google for self-mutating. There may be a hint there.
You may need to requery your block. I'll have a deeper look later today if I have time.
David
[Updated on: Tue, 07 June 2005 21:43] Report message to a moderator |
|
|
|
|
|
| Re: frm 40657 error [message #122715 is a reply to message #122674] |
Wed, 08 June 2005 03:01 |
harmeet.gambhir
Messages: 30
Registered: June 2005
|
Member |
|
|
thanks a lot for your reply djmartin..
the results of user_updatable_colums are shown below..
TABLE_NAME COLUMN_NAME UPD
------------------------------ ------------------------------ ---
CHALLAN_ITEM_DETAILS_V VENDOR_ID NO
CHALLAN_ITEM_DETAILS_V CHALLAN_ID NO
CHALLAN_ITEM_DETAILS_V CHALLAN_ITEM_ID YES
CHALLAN_ITEM_DETAILS_V ITEM_ID YES
CHALLAN_ITEM_DETAILS_V ITEM_NAME NO
CHALLAN_ITEM_DETAILS_V ITEM_QTY YES
CHALLAN_ITEM_DETAILS_V UOM_ID YES
CHALLAN_ITEM_DETAILS_V UOM_NAME NO
CHALLAN_ITEM_DETAILS_V ITEM_RATE YES
CHALLAN_ITEM_DETAILS_V ITEM_STATUS YES
i tried with on-update trigger in forms with the following code but the same errror occurs..
on-update trigger code:
declare
mrn_nu number;
begin
select mrn_seq.currval into mrn_nu from dual;
if :challan_item_details_v.item_status = 'Y' then
insert into mrn_ch_details(vendor_id,mrn_id,challan_id,item_id,qty_received,
qty_accepted,uom_id,item_rate) values(:challan_item_details_v.vendor_id,mrn_nu,
:challan_item_details_v.challan_id,:challan_item_details_v.item_id,
:challan_item_details_v.item_qty,:challan_item_details_v.item_qty,
:challan_item_details_v.uom_id,:challan_item_details_v.item_rate);
update challan_item_details set item_rate=:challan_item_details_v.item_rate,
item_status=:challan_item_details_v.item_status
where challan_item_id=:challan_item_details_v.challan_item_id;
end if;
end;
it is strange that first time there is no problem in saving ..the error occurs only on the second and consequent saves..
the problem still remains..please help me out..
thanks in advance to all of you..
harmeet
|
|
|
|
| Re: frm 40657 error [message #122728 is a reply to message #122569] |
Wed, 08 June 2005 05:13 |
A Ikramur Rahman
Messages: 81
Registered: May 2004
|
Member |
|
|
user_updatable_colums clearly shows that item_rate and item_status are updatable columns. Moreover, it is a database block and therefore oracle form will take care of updation of these two columns automatically when the values are changed by the front end user in these columns.
So, in your ON-UPDATE trigger try to give only insert part. i.e.
declare
mrn_nu number;
begin
select mrn_seq.currval into mrn_nu from dual;
if :challan_item_details_v.item_status = 'Y' then
insert into mrn_ch_details(vendor_id,mrn_id,challan_id,item_id,qty_received,
qty_accepted,uom_id,item_rate) values(:challan_item_details_v.vendor_id,mrn_nu,
:challan_item_details_v.challan_id,:challan_item_details_v.item_id,
:challan_item_details_v.item_qty,:challan_item_details_v.item_qty,
:challan_item_details_v.uom_id,:challan_item_details_v.item_rate);
end if;
end;
Hope this helps.
|
|
|
|
| Re: frm 40657 error [message #122749 is a reply to message #122728] |
Wed, 08 June 2005 06:47 |
harmeet.gambhir
Messages: 30
Registered: June 2005
|
Member |
|
|
thanks rahman for ur reply..
this is actually the problem that it is not updating those two columns(item_rate,item_status) without the update statement.that's why i had explicitily pasted the following code in instead of update trigger:
update challan_item_details set item_rate=:challan_item_details_v.item_rate,
item_status=:challan_item_details_v.item_status
where challan_item_id=:challan_item_details_v.challan_item_id;
one thing i would like to state here that this block is being retrieved on basis of vendor_id being chosen from block 1 above i.e this data block has a relation with another block having only single item as vendor_id...which on querying populates the record into this view...ie. challan_item_details_v block ..
thanks in advance..
harmeet
|
|
|
|
| Re: frm 40657 error [message #122757 is a reply to message #122569] |
Wed, 08 June 2005 07:23 |
A Ikramur Rahman
Messages: 81
Registered: May 2004
|
Member |
|
|
Harmeet,
What you do is, drop your INSTEAD OF Trigger for the time being and try to update the view directly through backend. i.e.
UPDATE challan_item_details_v
SET item_rate<<some value>>,
item_status=<<some value>>
WHERE challan_item_id=<<some value>>;
If this updation is successful, then try to create a simple form with only one database block (based on this view) and update the above fields for various records.
Do let us know, if the same error persists.
|
|
|
|
| Re: frm 40657 error [message #122759 is a reply to message #122757] |
Wed, 08 June 2005 07:40 |
harmeet.gambhir
Messages: 30
Registered: June 2005
|
Member |
|
|
thanks rahman for the reply..
as told by you, i tried updating the challan_item_details_v from backend(sqlplus) but it's updating only one column item_rate but not item_status column..the update query is as follows:
update challan_item_details_v set item_rate=890,
item_status='A' where challan_item_id=231
(i.e the query changed the item_rate to 890 but did not change the item_status column having default value of 'U' to 'A'.
pl. help
thanks in advance..
harmeet
|
|
|
|
|
|
| Re: frm 40657 error [message #122898 is a reply to message #122769] |
Thu, 09 June 2005 01:36 |
harmeet.gambhir
Messages: 30
Registered: June 2005
|
Member |
|
|
thanks rahman for your reply..
i tried by creating a new view both with item_status='U' and without it..the error is still there
the problem is that i have to check a condition that only when
the item_status has changed to 'Y'. i.e item_status='Y'
only then the records should be inserted into mrn_ch_details table..
the problem is thet sometimes the error frm :40654 occurs and sometime the error frm :40657 occurs ..
these errors are really posing a problem in development as i have some more modules to create which require the data block to be based on views..
pl. help
thanks in advance..
harmeet
|
|
|
|
| Re: frm 40657 error [message #122951 is a reply to message #122898] |
Thu, 09 June 2005 06:06 |
A Ikramur Rahman
Messages: 81
Registered: May 2004
|
Member |
|
|
Please mention whether you can able to update both the fields (i.e. item_rate and item_status) directly onto the view CHALLAN_ITEM_DETAILS_V through back-end (without using any INSTEAD OF triggers). Create your view without giving the condition b.item_status='U', since if you give
item_status='U' on your view definition, you wont't able to update the view as in your update statement you are giving condition item_status='Y' which is contradictory to the above.
If you can successfully update both the fields through backend, then I don't see any reason why it cannot be done through a simple form. create a simple form with this view as database block and try to update multiple records for these two fields.
If these forms errors are not returned, then there is some logical error in your actual code. If errors are still coming, then it could be due to missing of some patches in your forms developer suite.
|
|
|
|
| Re: frm 40657 error [message #122961 is a reply to message #122951] |
Thu, 09 June 2005 06:32 |
harmeet.gambhir
Messages: 30
Registered: June 2005
|
Member |
|
|
thanks rahman for your reply..
i will work on the tips given by you..in the meantime can u give brief idea as to what missing patches are you talking about for forms developer suite..the developer suite i'm working on is release 2 9.02(forms 9i).
thanks in advance..
harmeet
|
|
|
|
| Re: frm 40657 error [message #123174 is a reply to message #122961] |
Fri, 10 June 2005 06:28 |
harmeet.gambhir
Messages: 30
Registered: June 2005
|
Member |
|
|
sorry for the late reply rahman
actually i have to use the condition item_status='Y'
as i'm selecting some challans through the form which after being selected for one mrn will never appear again for selection.
as you suggested i tried with on update trigger in forms (i.e not using instead of update triggers for the form), but now it's giving frm 40654 error when i change something in the block below
pl. help
thanks in advance..
harmeet
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
