Let's say that I want to write a trigger which will check in the Contacts form whether a contact already exists with the email address which has just been typed in. 'PHONEBOOK' is the name of the contacts table, ':$.EMAIL' is the name of the email field and ':$.PHONE' is the id field.
Once, I would have written the trigger like this
:COUNT = 0; SELECT COUNT (*) INTO :COUNT FROM PHONEBOOK WHERE EMAIL = :$.EMAIL AND PHONE <> :$.PHONE; WRNMSG 500 WHERE :COUNT > 0;
This code counts how many existing records in the PHONEBOOK table have the given e-mail. A warning message (number 500) is displayed if the result is greater than 0. One day, I realised that this is unsophisticated: I don't care about the actual number of duplicates but I do care that a duplicate exists. Enter more sophisticated code
WRNMSG 500 WHERE EXISTS (SELECT 1 FROM PHONEBOOK WHERE EMAIL = :$.EMAIL AND PHONE <> :$.PHONE);
Today I discovered an even more sophisticated way of doing the same thing
WRNMSG 500 FROM PHONEBOOK WHERE EMAIL = :$.EMAIL AND PHONE <> :$.PHONE;
Theoretically, this version should be faster than the previous version which should be faster than the naive version, although practically speaking I doubt whether anyone will notice the difference. This syntax does not appear in the Priority Developer's Bible; what does appear is
Looking at pre-defined triggers, I found a few variations:WRNMSG msg_number [ WHERE condition ];
GOTO 1 FROM PART WHERE ... SELECT 50 INTO :$.GO FROM ERRMSGS WHERE ...
I found a good use for this syntax, along with another improvement introduced in version 17: NFILE. This is documented as 'Select NFILE if the linked file comprises a group of records and you want the link table to remain empty when the user enters * or leaves the field empty.' NFILE should be used when getting a linked file when the base table contains many records - such as parts, customers and suppliers. Previously, one would declare the parameter to be of type FILE; the program would take a relatively long time to fill the table if all the records were chosen (by *). NFILE allows one to save this copying; the original table will be used if the field either contains * or is left empty.
But how does one check? In the past few months, I've been writing code like this
But now I can writeLINK ORDERS TO :$.ORD; ERRMSG 1 WHERE :RETVAL <= 0; :COUNT = 0; SELECT COUNT (*) INTO :COUNT FROM ORDERS WHERE ORD > 0; GOTO 1 WHERE :COUNT > 0; UNLINK ORDERS; LABEL 1;
Concise and to the point. So, one more undocumented nugget.LINK ORDERS TO :$.ORD; ERRMSG 1 WHERE :RETVAL <= 0; GOTO 1 FROM ORDERS WHERE ORD > 0; UNLINK ORDERS; LABEL 1;