Triggers are sometimes not being ran in the RTC environment

Alex_Chow · 2012-04-23

We have a client that heavily uses the RTC environment. They are setup with full manufacturing (with MRP calculations) and warehousing with serial numbers. They also use E-Ship with UPS/Fedex integration. They're on NAV2009 R2.

For this particular environment, we're encountering problems where table triggers are SOMETIMES not being ran. For example:
- If the user enters the Quantity through RTC, the Quantity Outstanding doesn't get populated.
- If the user inserts a new Item, the code on the OnInsert isn't ran
- When a new sales order is entered, the No. Series isn't updated with the next number

These are just some of the problems, there are more instances such as this that all points to the OnValidate, OnDelete, OnModify, and OnInsert aren't being ran sometimes on the tables.

Needless to say, I cannot replicate this on their environment as well. But the problem all seems that the triggers are not being ran.

Anyone else running into problems like this? Or am I crazy?

Chinmoy · 2012-04-24

Have never faced such issue with my implementations. Did you check whether the filters are proper? I mean is it something like some records are getting filtered out of scope while you are running the pages from RTC?

Chn

Belias · 2012-04-24

Did you try to use the last build? If i remember welll, some builds change the behaviour of the triggers. For example, with a particular build, the onaftergetrecord trigger of the page is not run as expected on list pages (when you change the record, the trigger is not run, but IT IS run when you open the page, for every record)

mihail_kolev · 2012-04-24

never happened to me too, but I've had similar situations and the problem was just ME

:

The problem with the population of the Quantity Outstanding - probably is some error in the logic or due to too many conditions for the validation. Happens just to forget what exactly is going on in there.

the OnInsert trigger? Sounds really strange but sometimes in a moment of frustration, you might forget when exactly is executed each trigger - not when you expected, but when NAV is programmed to execute it

so, probably the last one is true, you are going crazy

P.S.: I don't say you are wrong, but always check and double check everything. Good luck.

Belias · 2012-04-24

correction: my post is a bit misleading here

with a particular build, the onaftergetrecord trigger of the page is not run as expected on list pages (when you change the record, the trigger is not run, but IT IS run when you open the page, for every record)

the bold part means: "when you move through different records"

Alex_Chow · 2012-04-24

Well, I've already checked the obvious before I came here. The problem with this is that it occurs inconsistently. At first, I suspected that the user was generating the data using dataport and not validating, but I've ruled that out since the user does not know how dataport works.

Again, we have many clients on RTC, but only this particular site is giving us the problem. This site is also transacting the most amount of data.

I was just wondering if anyone with heavy transactions using RTC is experiencing the same thing as me.

I'm now leaning towards hardware setup and SQL settings.

David_Singleton · 2012-04-24

Personally I would want to eliminate all the "valid" possibilities by going through them and use the Sherlock Holmes principle

When you have eliminated the impossible, whatever remains, however improbable, must be the truth

to work out what could have happened.

I would start with direct access to the database via t-sql, odbc c/front etc.

I once had a client that worked out how to get data into excel directly with ODBC, but didn't realize that changes in his spread sheet were being written back to the database.

Then of course code, its quite possible for some very simple innocuous piece of code some where to cause this problem.

Reports also are dangerous, because the user has F9 access to code. If ever you need to write code in a form but don't have a developers license, then you can copy paste the entire form into the request form in a report and it will work exactly like a report AND you can write code. It's extremely unlikely but its possible the client is doing this.

Ultimately though if this is a bug, the only way to find it is going to be to eliminate all the possibilities then fall back on the impossibility that there is a bug in Navision.

davmac1 · 2012-04-24

I agree with David - check for ways the client could be getting information into the system that bypasses NAV triggers.
Have you tried using the change log on selected fields and tables that are skipping the validate logic?

DenSter · 2012-04-24

David Singleton wrote:

Ultimately though if this is a bug, the only way to find it is going to be to eliminate all the possibilities then fall back on the impossibility that there is a bug in Navision.

No it's a bug, I've tried everything, and I've read and studied every manual. I have all certifications so I already know everything. Please to guide me what code to write and where plzzzzz

Alex_Chow · 2012-04-24

If they used external tools to load data in NAV, then none of the triggers would be executed. In this case, only some of the triggers were not executed on a specific record.

If it's a bug, then I would suspect more people would encounter the same issues as I have. Apparently, none has so it's specific to this installation I guess.

I do have a feeling that it's a bug strictly in the RTC environment on a very certain user action in RTC, the problem is that I cannot replicate it. When I asked the user on how they enter the data for the records in question, the usual answers is they don't remember...

Alex_Chow · 2012-04-24

Is it possible that NAV or SQL will skip some code if there are too much code queued up?

kapamarou · 2012-04-24

If it is possible, ask your user to do his everyday work with SQL Profile running. Maybe there is an exception that is not caught by NAV.

davmac1 · 2012-04-25

If it is possible for NAV or SQL to skip code then we are in big trouble.
What I have found is possible is users (especially new users) find ingenuous ways to break the system that the developers never thought of.
Have you looked at alternate ways of getting to the tasks the users are performing?
Is everything being done through a NAV client or are you running any web services? Any custom routines that are updating the tables without validations?

Alex_Chow · 2012-04-25

No webservices, no C/ODBC, no C/FRONT, no direct SQL programs, no dataports, no xmlports, no process only reports. The users are only trained to enter data through RTC pages.

One thing about the problem with No. Series not incrementing, I did a test a while back putting in COMMIT, then the problem seemed to go away. However, COMMIT caused other problem so I had to remove it.

From what I understand of COMMIT, it writes whatever is in cache to the disk. So is it possible that the cache is so full that a lot of additional tasks are ignored?

davmac1 · 2012-04-25

I know Mibuso is wonderful, but sometimes Microsoft is the best solution. Have you asked them to look at it?

If the problem is executable based, then you have found a major bug.
NAV has fixed problems in the past where NAV and SQL did not work together properly - like the exists option in flowfields in version 5.

Alex_Chow · 2012-04-25

davmac1 wrote:

I know Mibuso is wonderful, but sometimes Microsoft is the best solution. Have you asked them to look at it?

If the problem is executable based, then you have found a major bug.
NAV has fixed problems in the past where NAV and SQL did not work together properly - like the exists option in flowfields in version 5.

They will only look at it if I can duplicate the problem. But I can't... All the errors are reported after the problem has already occured...

I've turned on the garbage collection command and it's suppose to enhance the RTC performance. Lets see if this resolves the problem. If it does, I'll blog and post about it.

David_Singleton · 2012-04-26

Alex Chow wrote:

From what I understand of COMMIT, it writes whatever is in cache to the disk. So is it possible that the cache is so full that a lot of additional tasks are ignored?

I don't think that is correct. COMMIT simply draws a line in the sand and says that all transactions up to this point and now committed. But those committed transactions could be in CACHE or In the Log File or written to the database.

If COMMIT worked as you say, then every time there was a commit, Navision would have to come to a complete halt, stop all processing and then wait until the cache was written to log and the log was written to the database. There are scenarios where that can take hours.

davmac1 · 2012-04-26

One area NAV is weak in, is anything that causes recursive processing. I have always seen an error when this happens though.

Are you doing anything with serial numbers that would cause a quantity to be processed individually for each serial number or something of this nature?

NAV 2013 - we get a debugger for RTC which will make this type of troubleshooting more practical.

Have you tried running a trace in SQL Server profiler?

Alex_Chow · 2012-04-26

davmac1 wrote:

Have you tried running a trace in SQL Server profiler?

I don't even know where to start tracing from. The problem appears randomly and across different users in the company.

I suspect I will get milliions of lines before the problem can even occur. And even if the problem occured, it may take a day or 2 before someone catches it. i.e. you can post without outstanding quantity if there are other lines with outstanding quantity.

davmac1 · 2012-04-30

One last thought (maybe) - any custom code that does not execute triggers?

Alex_Chow · 2012-04-30

davmac1 wrote:

One last thought (maybe) - any custom code that does not execute triggers?

Nope. No EXIT, QUIT, SKIP or anything else that would cause table triggers to skip.

mdPartnerNL · 2012-05-02

An option would be to create a logtrigger table with two fields; Entry No (autoincrement=yes) and triggername (text 50)
In your triggers add to the firstline of the trigger:
clear(logtrigger)
logtrigger.name = 'triggername'
logtrigger.insert(true);

and check after a few days if you are missing an entry. To keep the table small and fast you could add some code in the logtrigger.insert trigger do check the numbering and delete the records every 2000 records.

Alex_Chow · 2012-05-02

mdPartnerNL wrote:

An option would be to create a logtrigger table with two fields; Entry No (autoincrement=yes) and triggername (text 50)
In your triggers add to the firstline of the trigger:
clear(logtrigger)
logtrigger.name = 'triggername'
logtrigger.insert(true);

and check after a few days if you are missing an entry. To keep the table small and fast you could add some code in the logtrigger.insert trigger do check the numbering and delete the records every 2000 records.

:shock:

That would mean I have to modify every table and codeunit?

mdPartnerNL · 2012-05-02

If you don't like the advice, no need to show the :shock:

Now I need to add the roling eyes :roll: and say "of course you don't need to add it to all the triggers, just the suspected ones. If you can't show Microsoft support when/where it's going wrong you will have a hard time resolving this". And again :roll:

Alex_Chow · 2012-05-02

Well, just an update.

I added the garbage collection to the RTC server and I haven't heard of any problems so far. But I'll keep monitoring and report back.

Alex_Chow · 2012-05-02

mdPartnerNL wrote:

If you don't like the advice, no need to show the :shock:

Now I need to add the roling eyes :roll: and say "of course you don't need to add it to all the triggers, just the suspected ones. If you can't show Microsoft support when/where it's going wrong you will have a hard time resolving this". And again :roll:

That's the problem. I don't know where its' most suspect. It just randomly occurs in every parts of NAV.

ichladil · 2012-05-03

Hello,

Recently I have been sorting this kind of issues by the "low level" checks on the relevant table. Therefore what I would do e.g. for incorrect Outstanding Qty. on Sales Line is that I would introduce a check in either SQL On Update trigger (that one may provide best results as it would capture even external access to DB from outside NAV), or in Codeunit 1 - OnDatabaseModify. That check would simply calculate itself the expected Sales Line Outstanding Qty. based on the system data and throw an error if it is wrong.

I have myself successfully used this type of check in other scenarios and it has lead me to the root cause of the problem quite quickly. It at least shows you where the issue is as the system should throw to the user the error in case of "inconsistency".

Regards,
Igor

jglathe · 2012-05-03

Hi Igor, thanks for the tip.

BR Jens

Alex_Chow · 2012-05-24

Just an update, so far, the client has not report any issues since the gccode has been implemented. Will continue to monitor and report. If by next month, nothing else is reported, then I guess the garbage collection is the key to resolve this.

Mauddib · 2014-11-05

Alex,

Somtimes it can help us not feel we are going crazy when someone else sees the Ghost too.

So just to inform you we are moving to 2009 Sp1 at the moment and I have seen exactly this behavior though not as extensive as yours.

Basically it is only happening on Sales Orders (so far) and as with you the Behavior appears to be entirely random. When entering a value in the Quantity field, sometimes the updates to fields like Outstanding Quantity and "Qty. To Ship" simply do not happen.

And like you we are entirely certain of NO external sources of data or input in this: Such as dataports, ODBC, SQL or anything.

jeffjoh · 2014-11-07

Mauddib,

You might want to look at the most recent hotfixes for the product. I recently had very similar issues with Sales Orders resolved by apply a hotfix.

Triggers are sometimes not being ran in the RTC environment

Comments

Categories