The ON UPDATE trigger

The ON UPDATE trigger defines an action named "update", dedicated to row modification of the current row of the record list.

Inside a DISPLAY ARRAY dialog, defining the ON UPDATE block will automatically create an "update" action for the dialog, that can be fired by the user with the Update button to modify an existing row. This action is implicitly defined as ROWBOUND; if no rows exist in the record list, the action is automatically disabled.

Best practice is to use the UNBUFFERED attribute with DISPLAY ARRAY modification triggers.

The ON UPDATE block typically implements an INPUT instruction, binding a record variable to the form fields of the current row in the TABLE container used by the DISPLAY ARRAY dialog.

After the INPUT dialog terminates, the int_flag register is used to validate or cancel the row modification, when the control goes back to the DISPLAY ARRAY dialog. If the user input is validated, the program can try to update the row into the database table. If the SQL UPDATE succeeds, assign the entered values to the current row of the program array. If the SQL UPDATE fails, display an error message and force the int_flag to TRUE in order to cancel the operation. If int_flag is TRUE, the DISPLAY ARRAY dialog resets the current row data as before the INPUT. If int_flag is FALSE, the DISPLAY ARRAY dialog keeps the current row values, with the data of the corresponding row of the program array.

The INPUT instruction must be defined with the FROM clause and the screen array of the form, with a screen array index equal to the current screen line returned by the scr_line() build-in function. This is the current line in the TABLE container, as seen by the user.

ON UPDATE code example:

  1      ON UPDATE
  2         LET x = DIALOG.getCurrentRow("sa_cust")
  3         LET rec = custarr[x]
  4         INPUT rec.* WITHOUT DEFAULTS FROM sa_cust[scr_line()].* ;
  5         IF NOT int_flag THEN
  6             TRY
  7                 UPDATE customer SET customer.* = rec.*
  8                        WHERE cust_num = rec.cust_num
  9                 IF sqlca.sqlerrd[3] == 0 THEN
 10                    ERROR "The row has been deleted by another user."
 11                    LET int_flag = TRUE
 12                 ELSE
 13                    LET custarr[x] = rec
 14                 END IF
 15             CATCH
 16                 ERROR SQLERRMESSAGE
 17                 LET int_flag = TRUE
 18             END TRY
 19         END IF

Note:

Line 1 declares the ON UPDATE trigger.
Line 2 assigns the current row index to the x variable defined as INTEGER.
Line 3 assigns all fields of the rec record to the correspondong field values of the current row of the custarr dynamic array.
Line 4 implements the INPUT dialog to let the user modify customer record data. Note that the INPUT uses the FROM clause, with the sa_cust screen array, at the current screen line returned by the build-in scr_line() function.
Line 5 checks the int_flag register.
Line 6 thru 17: here we try to UPDATE the customer record in the SQL table. This SQL statement may fail, because of some SQL table constraints (NOT NULL, UNIQUE, etc). Therefore, we want to protect the code in a TRY/CATCH block and show an error message in case of problem. If the SQL fails we must set int_flag to TRUE to force the dialog to cancel the new row creation in the record list. Additionally, if the row is deleted from the database table since the last time we have fetched the rows, we want to warn the user. The sqlca.sqlerrd[3] register can be used to check the number of row processed by the last UPDATE SQL statement.
Lines 7 thru 9 implement the SQL UPDATE statement, using the rec record values. The use of the .* notation instructs the compiler to expand the SET clause to all table columns and record members. You can check the resulting SQL with fglcomp -S:
```
fglcomp -S custlistmod.4gl
...
custlistmod.4gl^94^UPDATE customer SET cust_num = ?,cust_name = ?,addr = ?,city = ?, ...
...
```
Line 9 checks sqlca.sqlerrd[3] for the number of rows processed by the UPDATE. If we get zero, it means that the row has already been deleted.
Line 13: If the UPDATE succeeded and found a row to update, we assign the values of the input record rec, to the current row of the custarr dynamic array. This is mandatory, otherwise the current row would show the older values, when the control goes back to the DISPLAY ARRAY dialog.