Saturday, January 09, 2016

Not playing by the rules

Today's story of frustration, told in excruciating detail, is about a problem which I faced in Priority and how it was solved. If this is not your cup of tea, then don't read on....

The story starts about a month ago, when one of the people in the company where I consult requested a minor improvement. They have defined a default warehouse for a small group of parts (each part has a warehouse specified) and they want that whenever such a part appears in a supplier's delivery note, the part will be entered into that warehouse's inventory. It often happens that parts get assigned to the wrong warehouse and my addition will correct that tendency.

Adding developments to screens in Priority is an activity which I dislike as it is sometimes very difficult to get what seems to be the correct code to work. Also, changes to screens affect everyone who is working, so frequently such development work has to be performed during down time. Theoretically, the screens are well-defined, having several triggers (or event handlers) which make development easy (or less hard). In a sense, this is very similar to programming in Delphi or any of the modern languages developed for Windows: one inserts one's own code which will be executed when the defined event occurs.

So I thought to myself that the event which interests me is when the part is entered into a row of the delivery note, or in Priority-speak, the post-insert trigger of the form INVOICEITEMS_P. The additional trigger is simple (I won't go into details here), but when I added it in the appropriate place, nothing happened when I tested it. I tried moving the code to a few other triggers but it was still not being executed. I then dropped the subject, knowing that the inherent frustration (the gumption trap) would not lead me to a solution; a fresh pair of eyes were needed.

A few days ago, I revived the subject. This time, I started by reading the standard code to see what happens when an order is inserted into a delivery note (this is what causes the lines to be added). The parent form (DOCUMENTS_P) has two triggers which interest me: post-insert and post-update; in both cases they call common code (BUF3) which reads the connected order and inserts the data into the delivery note lines.

I was astounded when I discovered that this code directly inserts the data into the TRANSORDERS table instead of employing an interface to do so. This is in direct contravenence to the instructions in the SDK (the developers' bible) which states Important! The records stored in Priority tables are always inserted into those tables via Priority forms. Never insert records directly into Priority tables, as this will bypass the integrity checks and other actions defined in the form triggers (p. 122). This direct insertion explains why my original code - based on the post-insert trigger of TRANSORDERS - was never called.

OK, I thought to myself: if you're not going to play by the rules, then neither am I. If you're going to insert data directly into the TRANSORDERS table, then I am going to update that same data directly. Thus I needed to write common code which would be called from private post-insert and post-update triggers of the parent form, in a similar manner to the standard code. Again, writing the code itself was very simple, but testing was another matter. I could see (by inserting debugging statements) that the code was being called, but the query which was supposed to return the rows in TRANSORDERS was returning empty every time. What could the problem be?

I took the dog out for a walk - always a good debugging technique - and realised that the computer was not lying (it never does): there really were no rows to be returned. In other words, my trigger was executing before the standard trigger were inserted the rows. How could this be? Standard triggers always execute before personal triggers!

It turns out that I had misunderstood that final statement. Looking in the SDK again, I come across this nugget: Standard and custom triggers are sorted alphabetically, so you should name your own triggers accordingly. For example, to run your own trigger after a standard POST-INSERT trigger, use POST-INSERT_AXXX or ZXXX_POST-INSERT (where XXX is part of the prefix you normally use for this customer). In other words, because my trigger was called ATLN_POST-INSERT, it comes before POST-INSERT in alphabetical order and so was executing first. I had confused 'standard triggers' with 'built-in triggers'.

So it was a simple matter to rename my triggers to ZTLN_POST-INSERT and ZTLN_POST-UPDATE, and voila! The code worked first time!

What I have learnt from all those frustrating hours (I reckon I spent about five hours on what should have been a simple exercise; I'll bill an hour and a half)?
  1. All triggers execute in alphabetical order. 
  2. Always read the source code to see how the program achieves something before trying to extend that functionality
The first lesson will be taken to heart, but I suspect that I will forget the second until I come across it again.

1 comment:

Yitzchok said...

I read this a while ago but having just posted this response to a question on the Priority forums I thought I'd respond to what you wrote ("I was astounded when I discovered that this code directly inserts the data into the TRANSORDERS table instead of employing an interface to do so.").

The software house can do what they want in their system. In fact at some point the table data has to be modified directly, and they do it in triggers too. The fact that you can read their code doesn't make it less "internal" to the application. That particular development rule applies to external programmers, not to them.

It's not that direct updates to the database are inherently dangerous. It's just that since all the logic to protect data integrity is in the screens - business logic and even referential integrity, sadly - they want you to use the interface process because your direct update might disregard some rule or other of theirs or just be buggy.

With private tables, they're really not going to care how you update the data so they won't complain if you do it directly. Yes, interfaces which use form logic will theoretically prevent you creating referential integrity anomalies, but if you build your form wrongly they won't :) But if you mess up your data in private tables you're not going to be asking them for help anyway.