Another user has modified the record

Wxyz1234Wxyz1234 Member Posts: 4
edited 2013-11-01 in SQL General
Hi !
We have an Update trigger to catch up records of few fields from Sales line table to another table being with another DB.
But that leads to error in Navision postings, like the below,
Another user has modified the record for this Sales line after you retreived it from the database. Enter your changes again in the updated window, or start the interrupted activity again
.

How to overcome this error??

Thanks !

Comments

  • krikikriki Member, Moderator Posts: 9,038
    But are you changing fields in the sales line? This changes the version of the record and thus NAV says that the record has changed. You should avoid writing in the sales line to avoid the error.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Wxyz1234Wxyz1234 Member Posts: 4
    kriki wrote:
    But are you changing fields in the sales line? This changes the version of the record and thus NAV says that the record has changed. You should avoid writing in the sales line to avoid the error.

    Hi ! Thanks for ur reply,
    But i am not writing into sales line, Jus picking up the values from the line at the time of the line getting updated and storing those to a another table.

    The error occurs only when the header getting changed and affects the lines and not when the row gets changed individually.
    Like, when the the Delivery date on header gets changed , it asks for update over rows, by this time alone the error occurs.
    Where as when the row delivery date is changed manually row by row the error is not appearing.

    Any guess??
  • krikikriki Member, Moderator Posts: 9,038
    I've seen the problem a lot of times.

    Probably this is happening:
    function1:
    LrecSalesLine.GET(...); // you get the record (it is version 1 now)
    
    Function2(LrecSalesLine); // you call another function giving this record as a parameter
    
    LrecSalesLine.modify; // you modify the line. NAV checks if the record in the DB is still version 1 and it is NOT because in "Function2" you just changed the version
    
    Function2 (IrecSalesLine) // you have NOT toggled the VAR for the variable, so it makes a copy of the buffer
    
    ...
    IrecSalesLine.modify; // you modify the line. NAV checks if the record in the DB is still version 1 and it is, so it writes the new version. in the DB there is now version 2
    

    Solutions:
    Or you put the VAR-toggle in Function2 if that is possible
    Or after calling Function2, you refresh the record, doing a GET or a FIND('=')
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Wxyz1234Wxyz1234 Member Posts: 4
    Hi !
    But i do have the Triggers in SQL in Sales Line Table...
    I am not aware of NAV C/AL codes... Seems u ve mentioned it for C/AL code ...

    is there a similar way to do it in SQL ?
    The Trigger looks like,

    Update Trigger looks like,
    Alter Trigger [UpdatingRows]
    on
    <Company Name>_$[Sales Line]
    as 
    If Exists (Select No from Inserted Where docTYpe = 1 and Status = 0)
    Begin
    Update BackUp.[dbo].[SalesLine]
    Set 
    UpdateDate = GetDate(), ProdNo = inserted.No
    End
    


    Thanks !!
  • krikikriki Member, Moderator Posts: 9,038
    Did you try everything WITHOUT the SQL-trigger? At first sight, the trigger should not give problems.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • David_SingletonDavid_Singleton Member Posts: 5,475
    Wxyz1234 wrote:
    But i am not writing into sales line,

    Are you sure?
    David Singleton
  • davmac1davmac1 Member Posts: 1,279
    I was advised by someone (Rashed?) years ago to use a cursor in the for update trigger, that way the timestamp field is not modified.
    This worked for me in version 4 SQL 2000, so give it a try.
    The regular read in the update trigger apparently updates the timestamp field in the table which is why you get the message.
  • strzzzstrzzz Member Posts: 5
    Hi
    did you find any solution to your problem? How should I use sql cursor to make this work ?
  • william_marcelinuswilliam_marcelinus Member Posts: 34
    Dear,

    This also happening on me last time we use trigger to update another record from sales line,
    because like kriki said those running trigger have different version of modify record which can
    cause locking on timestamp which is always updated when Sales Line record updated or modify
    from NAV Service. i suggest that you use Predefined Event on Table Sales Line without touching
    SQL Server side.
Sign In or Register to comment.