Options

Strange behavior with IF INSERT(TRUE)

vbabicvbabic Member Posts: 2
Hello everybody!

I have encountered a strange behavior regarding the IF INSERT statement. According to Navision help, if you call IF INSERT instead of just INSERT, I must handle the error, and no error is given by runtime. Also, according to help, if I call INSERT(TRUE) instead of just INSERT or INSERT(FALSE), Navision calls the OnInsert. This made me think that if I call IF INSERT(TRUE) that no error which occurred in OnInsert trigger will occur, and whatever error occurred there I would just get FALSE value returned.

However, this is not true. If there is an error of whichever kind in OnInsert trigger, this error will stop the execution with its error message, even if INSERT is called with IF INSERT.

This basically means that only time that IF INSERT will return FALSE instead of just throwing the error is when primary key violation occurs, because this error will occur even when INSERT is called with no parameter or RunTrigger=FALSE.

This also means that there is no point in calling IF INSERT(TRUE) with parameter of RunTrigger=TRUE, because it will behave just as INSERT(TRUE) if you need to check beyond primary key violation errors.

Further, this means that in case I want my insert to return false and not to give any error message in case any OnInsert trigger code fails, the only workaround would be actually copying the OnInsert trigger code into a new function which would return true or false and make sure no errors occur there, then call this function before actually calling INSERT. Pseudocode would go like this:

// begin pseudocode

// OnInsert trigger
OnInsert
BEGIN
DoSomething(); // Error may occur here
DoSomethingElse; // Error may occur here
END;

// Function which mimics the OnInsert behavior
OnInsertCheck: boolean
BEGIN
IF MakeSureDoSomethingWillSucceed() THEN BEGIN
DoSomething();
END ELSE
EXIT(FALSE);
IF MakeSureDoSomethingElseWillSucceed() THEN BEGIN
DoSomethingElse();
END ELSE
EXIT(FALSE);
EXIT(TRUE);
END;

// Code which would normally call IF INSERT(TRUE) THEN...
BEGIN
// Instead of calling this:
// IF INSERT(TRUE) THEN...
// This should be called
IF OnInsertCheck AND INSERT THEN BEGIN
// ... do whatever you would do if IF INSERT(TRUE) succeeded
END ELSE BEGIN
// ... do whatever you would do if IF INSERT(TRUE) failed
END;
END;

// end pseudocode

Why does Navision behave like this? And why is this not documented in the documentation?

Best regards,

Vjeko
----
Altum videtur, quidquid latine dictum sit.

Comments

  • Options
    SteveOSteveO Member Posts: 164
    After reading the documentation again, I think it is a little misleading.
    However, after looking at the example they give it does sort of hint towards what it is really intended for.

    Why can't you handle your errors in the actual Insert trigger of the table?
    You could set a flag or use some sort of identification to determine whether or not the error should be shown or ignored. IMHO This might be a bit easier to maintain than having to maintain 2 semi-identical blocks of code.
    This isn't a signature, I type this at the bottom of every message
  • Options
    KowaKowa Member Posts: 918
    The return value of INSERT is most valuable when determining whether a record has to be inserted or modified. When updating records with several primary key fields ( e.g. new prices for the Sales price table) you only need to assign the new values to all fields and then
    IF NOT SalesPrice.INSERT(TRUE) then
     SalesPrice.MODIFY(TRUE)
    
    Kai Kowalewski
  • Options
    DenSterDenSter Member Posts: 8,304
    Just to add something to the discussion :)
    IF NOT SalesPrice.INSERT(TRUE) then
     SalesPrice.MODIFY(TRUE)
    
    Better code would be (also in relation to better performance, especially on SQL Server):
    IF SalesPrice.GET THEN
      SalesPrice.MODIFY(TRUE)
    ELSE
      SalesPrice.INSERT(TRUE)
    
  • Options
    PhennoPhenno Member Posts: 630
    DenSter wrote:
    Just to add something to the discussion :)
    IF NOT SalesPrice.INSERT(TRUE) then
     SalesPrice.MODIFY(TRUE)
    
    Better code would be (also in relation to better performance, especially on SQL Server):
    IF SalesPrice.GET THEN
      SalesPrice.MODIFY(TRUE)
    ELSE
      SalesPrice.INSERT(TRUE)
    


    hm... you do SalesPrice.MODIFY because you changed a value of sam fields and want to save it.

    If you do SalesPrice.GET before SalesPrice.MODIFY(TRUE), it will overwrite your modification of values in fields cause he will get a record, am I right? Or it just places pointer to a record?
  • Options
    andreofandreof Member Posts: 133
    Yes i think you are right, that piece of code will not work.
    Andre Fidalgo
    My world: Dynamics NAV,SQL and .NET

    CEO at Solving Dynamics
    http://www.solvingdynamics.com
  • Options
    DenSterDenSter Member Posts: 8,304
    You don't really get the concept of a code snippet do you.... :shock:

    Of course you wouldn't do the GET after you modified the fields. You would check for existence of the record before you start modifying it, and you would initialize it properly if it doesn't exist. Waiting until the very end of your code to determine if you are inserting a record is simply not well written code.

    The point is that IF NOT INSERT THEN MODIFY is not very efficient code, especially considering the number of database reads it has to perform. [/code]
  • Options
    KowaKowa Member Posts: 918
    edited 2006-09-26
    This would work :
    IF SalesPrice.GET(PrimKeyField1,PrimKeyField2,PrimKeyField3,....) THEN
      SalesPrice.MODIFY(TRUE)
    ELSE
      SalesPrice.INSERT(TRUE)
    

    The problem is, if the primary key is extended , the code above will have to be changed to cater for the new primary key field
    IF NOT SalesPrice.INSERT(TRUE) then
     SalesPrice.MODIFY(TRUE)
    

    may not be the most efficent, but it is safer, because it will work with any number of primary key fields.
    Kai Kowalewski
  • Options
    andreofandreof Member Posts: 133
    And you dont know the concept of "ups, this was not exactly what i meant, let me explain better"
    Andre Fidalgo
    My world: Dynamics NAV,SQL and .NET

    CEO at Solving Dynamics
    http://www.solvingdynamics.com
  • Options
    DenSterDenSter Member Posts: 8,304
    ehm no I knew exactly what I meant my friend :mrgreen:
  • Options
    DenSterDenSter Member Posts: 8,304
    So here's the extended code snippet, for those of you who have trouble imagining beyond what is in the samples :whistle: :wink: :
    IF NOT Rec.GET(Field1,Field2,etc.) THEN BEGIN
      Rec.INIT;
      Rec.Field1 := value1; // you have to initialize key values
      Rec.Field2 := value2;
      Rec.etc...... // you get the point here, primary key values only though)
      Rec.INSERT(TRUE); // can also be FALSE, whatever you need
    END ELSE BEGIN
      // there's really not much to do here, since we have the existing record
      // and we don't need to initialize any fields at this point
    END;
    
    // now comes the modification code
    Rec.Fieldx := valuex;
    DoFunctionCalls();
    // run any other code that is needed here
    
    Rec.MODIFY(TRUE); // or FALSE, whatever you need
    

    The point is, you should not wait until the last line of code to question whether the record already exists. That should be one of the first things you do. IF NOT INSERT THEN MODIFY is simply not very good programming.
  • Options
    PhennoPhenno Member Posts: 630
    DenSter wrote:
    ehm no I knew exactly what I meant my friend :mrgreen:

    Yes, you did but, we didn't...
    In a context of a thread, at first glance, it lookes to me as a problem, like I asked in my first post. And, you are not talking to your self but to everybody ;)

    Enihau, thx for the tip.
  • Options
    KowaKowa Member Posts: 918
    It is not my invention :wink:, IF NOT INSERT THEN MODIFY is used in standard code too e.g. in Function FlushAnalysisViewEntry in Codeunit 7150 Update Item Analysis View.

    If the form for the table has the DelayedInsert property set to yes , it is recommendable to wait with the insert till all fields are set up, otherwise you may receive insert errors.
    Kai Kowalewski
  • Options
    Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    If you read (SQL) performance doc's you will see that IF INSERT is not recomended to use.
  • Options
    KowaKowa Member Posts: 918
    So this must be one of the reasons why so many people complain about the performance of the SQL Server. Still a lot of code optimization to do. Good ol' native server. :lol:
    Kai Kowalewski
  • Options
    PhennoPhenno Member Posts: 630
    Kowa wrote:
    So this must be one of the reasons why so many people complain about the performance of the SQL Server. Still a lot of code optimization to do. Good ol' native server. :lol:

    Cause of 3tier strategy of NAV5, I wonder, will they tune the code for SQL Server...
  • Options
    Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Unfortunately there are no official statements about this, but I somehow it seems unavoidable...
  • Options
    DenSterDenSter Member Posts: 8,304
    Kowa wrote:
    So this must be one of the reasons why so many people complain about the performance of the SQL Server. Still a lot of code optimization to do. Good ol' native server. :lol:
    It sure is one of those things that add to the mix. :mrgreen:

    You know NAV was developed specifically for the NAV Database Server, they never intended to go to SQL Server until the market demanded it. Microsoft themselves didn't start developing NAV specifically for SQL Server until 4.0, maybe even SP1 or 2. If you put it in that context the code 'problems' are not that big.

    Don't count on them revising the existing code for these types of issues though, that would bring many upgrade problems. I would personally like them to have a 'SQL Server Edition' of NAV with optimized keys and code, so you have a choice. I doubt that will happen though.
Sign In or Register to comment.