Strange behavior with IF INSERT(TRUE)

vbabic · 2006-09-26

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

SteveO · 2006-09-26

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.

Kowa · 2006-09-26

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)

DenSter · 2006-09-26

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)

Phenno · 2006-09-26

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?

andreof · 2006-09-26

Yes i think you are right, that piece of code will not work.

DenSter · 2006-09-26

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]

Kowa · 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.

andreof · 2006-09-26

And you dont know the concept of "ups, this was not exactly what i meant, let me explain better"

DenSter · 2006-09-26

ehm no I knew exactly what I meant my friend

DenSter · 2006-09-26

So here's the extended code snippet, for those of you who have trouble imagining beyond what is in the samples :whistle:

:

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.

Phenno · 2006-09-27

DenSter wrote:

ehm no I knew exactly what I meant my friend

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.

Kowa · 2006-09-27

It is not my invention

, 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.

Marije_Brummel · 2006-09-27

If you read (SQL) performance doc's you will see that IF INSERT is not recomended to use.

Kowa · 2006-09-27

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.

Phenno · 2006-09-27

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.

Cause of 3tier strategy of NAV5, I wonder, will they tune the code for SQL Server...

Marije_Brummel · 2006-09-27

Unfortunately there are no official statements about this, but I somehow it seems unavoidable...

DenSter · 2006-09-27

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.

It sure is one of those things that add to the mix.

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.

Howdy, Stranger!

Quick Links

Categories

Strange behavior with IF INSERT(TRUE)

Comments