Sunday, December 06, 2009

FRM-40654 Record has been updated by another user. Re-query to see the change

I had a form with master detail relationship. At commit, I had to validate that sum of a certain column in detail block must equal to 100. For example child block has three records and column percent has value of 10, 40 and 60 then commit should fail because sum of percent column is 110 i.e. not equal to 100.

In order to enforce this validation (Note: I am modifying a form after long time), I created a dummy third block based on same database table as master block, made this block non-visible and had only one column. Whenever a record is changed in detail block, I would update third block column to some dummy value and on-insert for third block, I checked that sum of percent should be 100 and if not then raise an error.

It created another problem. After saving, record status in third block became QUERY and now if I change anything in second block, I would get error ‘FRM-40654 Record has been updated by another user. Re-query to see the change.’ In order to get rid of this error I created POST-INSERT trigger for thirds block and did the following

Set_Record_Property
( NAME_IN ('SYSTEM.TRIGGER_RECORD')
, NAME_IN ('SYSTEM.TRIGGER_BLOCK')
, STATUS
, NEW_STATUS
) ;

It solved FRM-40654.

If there is any better way to handle these situations, please let me know.

1 comment:

  1. hii..

    if i out 'NEW_STATUS', the previous record will no show.

    so.. i put 'QUERY_STATUS'

    Set_Record_Property (
    NAME_IN ('SYSTEM.TRIGGER_RECORD'), NAME_IN ('SYSTEM.TRIGGER_BLOCK'), STATUS,
    QUERY_STATUS ) ;

    ReplyDelete