| FRM-40654: Record has been updated by another user. Re-query to see change [message #438586] |
Mon, 11 January 2010 02:02  |
babar82
Messages: 108
Registered: March 2009
Location: Karachi
|
Senior Member |
|
|
Hi,
My form is based on a complex view based on two tables. There is an update code and an insert code written in the ON-INSERT and ON-UPDATE triggers respectively. After I commit the form and try to update any field, the system gives the error " FRM-40654 Record has been updated by another user. Re query to see change."
I am using Forms6i.
Kindly help..
|
|
|
|
|
|
|
|
|
|
| Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #438633 is a reply to message #438586] |
Mon, 11 January 2010 05:31  |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You get record has been updated by another user when the contents of your datablock differs from the contents of the record(s) it is populated from in the database but the datablock is unchanged as far as forms is concerned (record_status = QUERY).
The 2 most common ways of getting it are:
1) Your table has database triggers on it that change the value of columns on the table on update or insert.
If you don't have DML Return set to TRUE this will give that error.
2) A manual update statement that sets one or more columns to different values than what is currently in the datablock.
Considering you are using on-insert and on-update triggers I strongly suspect you are suffering from number 2.
|
|
|
|
|
|
| Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #438847 is a reply to message #438586] |
Tue, 12 January 2010 10:24 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Query the record in a seperate sqlplus session (assuming the changes are commited), if not you can use a cursor to select the current values - stick a button in the form, code it to select the current record and display the values.
Or just check your on-insert/update triggers for any code that would set a db column to a different value to the corresponding datablock item.
If you post the code of those triggers we can take a look and see if we can spot anything.
The code in the post-query is not going to make any difference to this.
|
|
|
|
| Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #438899 is a reply to message #438847] |
Tue, 12 January 2010 22:34 |
babar82
Messages: 108
Registered: March 2009
Location: Karachi
|
Senior Member |
|
|
The code on ON-UPDATE trigger is
update emp_all_ded
set
ALL_DED_ID = :emp_all_ded.all_ded_id,
AMOUNT = :emp_all_ded.amount,
FROM_DATE = :emp_all_ded.from_date,
TO_DATE = :emp_all_ded.to_Date,
OFF_ORDER_NO = :emp_all_ded.off_order_no,
OFF_ORDER_DATE = :emp_all_ded.off_order_date,
updated_by = :global.payroll_user,
updation_DATE = sysdate
where
emp_all_ded_id = :emp_all_ded.emp_all_ded_id;
ON-INSERT Trigger Code is
insert into emp_all_ded
(EMP_ALL_DED_ID,
EMPID ,
ALL_DED_ID,
AMOUNT ,
FROM_DATE,
TO_DATE,
OFF_ORDER_NO,
OFF_ORDER_DATE,
CREATED_BY ,
CREATION_DATE_TIME)
values
(:emp_all_ded.emp_all_ded_id,
:emp_all_ded.empid,
:emp_all_ded.all_ded_id,
:emp_all_ded.amount,
:emp_all_ded.from_date,
:emp_all_ded.to_Date,
:emp_all_ded.off_order_no,
:emp_all_ded.off_order_date,
:emp_all_ded.created_by,
:emp_all_ded.creation_date_time);
exception when others then
message(dbms_error_text);
|
|
|
|
|
|
|
|
|
|
| Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #439066 is a reply to message #439063] |
Wed, 13 January 2010 22:58 |
babar82
Messages: 108
Registered: March 2009
Location: Karachi
|
Senior Member |
|
|
Well I have solved the problem for ON-UPDATE trigger. Actually the updated_by and updation_time datablock fields contain old values while the values stored in the database contain were new. So I changed the ON-UPDATE Trigger like this..
update emp_all_ded
set
ALL_DED_ID = :emp_all_ded.all_ded_id,
AMOUNT = :emp_all_ded.amount,
FROM_DATE = :emp_all_ded.from_date,
TO_DATE = :emp_all_ded.to_Date,
OFF_ORDER_NO = :emp_all_ded.off_order_no,
OFF_ORDER_DATE = :emp_all_ded.off_order_date,
updated_by = :updated_by, (instead of :global.payroll_user)
updation_date = :updation_date (instead of sysdate)
where
emp_all_ded_id = :emp_all_ded.emp_all_ded_id;
But ON-INSERT Trigger is still causing problems.
|
|
|
|
| Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #439091 is a reply to message #438586] |
Thu, 14 January 2010 03:31 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I thought that might be the issue with the on-update.
As for the insert - the on-insert trigger only references items from the datablock, so can't have the same cause as the on-update. The only thing I can think of is you've got a database insert trigger on one or more of the tables the view is based on and this trigger is changing one or more of the columns.
If that's the case you're going to have to add a RETURNING clause to the insert statement to get the modified value(s).
[Updated on: Thu, 14 January 2010 03:34] Report message to a moderator |
|
|
|
|
|
|
|
| Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #439370 is a reply to message #439234] |
Fri, 15 January 2010 22:48 |
babar82
Messages: 108
Registered: March 2009
Location: Karachi
|
Senior Member |
|
|
I have written the following in ON-INSERT trigger using Returning clause
insert into emp_all_ded
(EMP_ALL_DED_ID,
EMPID ,
ALL_DED_ID,
AMOUNT ,
FROM_DATE,
TO_DATE,
OFF_ORDER_NO,
OFF_ORDER_DATE,
CREATED_BY ,
CREATION_DATE_TIME)
values
(:emp_all_ded.emp_all_ded_id,
:emp_all_ded.empid,
:emp_all_ded.all_ded_id,
:emp_all_ded.amount,
:emp_all_ded.from_date,
:emp_all_ded.to_Date,
:emp_all_ded.off_order_no,
:emp_all_ded.off_order_date,
:emp_all_Ded.created_by ,
:emp_all_ded.creation_date_time)
returning
emp_all_Ded_id,
empid,
all_Ded_id,
amount,
from_date,
to_date,
off_order_no,
off_order_date,
created_by,
creation_date_time
into
:emp_all_ded.emp_all_ded_id,
:emp_all_ded.empid,
:emp_all_ded.all_deD_id,
:emp_All_ded.amount,
:emp_all_ded.from_date,
:emp_all_ded.to_date,
:emp_all_ded.off_order_no,
:emp_All_ded.off_order_date,
:emp_all_ded.created_by,
:emp_all_ded.creation_date_time
;
But when I save a new record and then try to update/delete it the system gives the error...
ORA-00439: feature no enabled :RETURNING clause from this client type.
|
|
|
|
|
|
|
|
| Re: FRM-40654: Record has been updated by another user. Re-query to see change [message #439406 is a reply to message #438586] |
Sat, 16 January 2010 04:20 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
DML Returning value is not going to help in this case.
It affects the default insert/updates forms generates for the datablocks.
Since on-update/insert overrides the default insert/updates it has no effect here.
However, what it does is add a returning clause to the default insert/update so I'm baffled as to why you are getting this error.
The only thing I think to suggest at this point is that you abandon using the on-update/insert triggers and instead base the datablock on a procedure that'll do the same job.
Hopefully this'll resolve the record has been updated issue.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
