Sunday, October 22, 2017

A conditional choose-field trigger (for use in a procedure)

In special circumstances, in which the value of one feature is dependent upon another, one needs to create a conditional choose-field trigger. Here's a forced example: let's say that there is a database of computer parts; certain computer models can accept certain types of disk drive. So first the user chooses a computer model; the disk drives will be displayed according to the computer model.


In order to implement the statement 'certain computer models can accept certain types of disk drive', there has to be a join-table, one field of which will be the computer model and one will be the disk drive.

A normal choose-field trigger looks like this:
SELECT DES, CODE FROM TEST_DISKDRIVES WHERE CODE LIKE '%' ORDER BY 2;
This trigger would be placed under the parameter defined under the appropriate step in the procedure. Assuming that the key of the computer model chosen has been stored in the variable :$.CM, one might be tempted to write the following trigger:
SELECT TEST_DISKDRIVES.DES, TEST_DISKDRIVES.CODE FROM TEST_DISKDRIVES, TEST_MODEL2DRIVE WHERE TEST_DISKDRIVES.CODE LIKE '%' AND TEST_DISKDRIVES.ID = TEST_MODEL2DRIVE.DRIVE AND TEST_MODEL2DRIVE.MODEL = :$.CM ORDER BY 2;
Unfortunately this won't work. The trigger is unaware of the supposedly global variable, :$.CM. The solution which I found to this problem is to define a table with two fields, 'USER', which will hold the current user number (i.e. SQL.USER), and 'VALUE', which contains the value chosen (i.e. :$.CM). The trigger then becomes
SELECT TEST_DISKDRIVES.DES, TEST_DISKDRIVES.CODE FROM TEST_DISKDRIVES, TEST_MODEL2DRIVE, TEST_USERVALUES WHERE TEST_DISKDRIVES.CODE LIKE '%' AND TEST_DISKDRIVES.ID = TEST_MODEL2DRIVE.DRIVE AND TEST_MODEL2DRIVE.MODEL = TEST_USERVALUES.VALUE AND TEST_USERVALUES.USER = SQL.USER ORDER BY 2;
This does work! One won't find this documented anywhere. Obviously it isn't something which one needs every day but it's the solution to a specific problem.

No comments: