The ON DELETE trigger

The ON DELETE trigger defines an action named "delete", to remove the current row of the record list.

Inside a DISPLAY ARRAY dialog, defining the ON DELETE block will automatically create a "delete" action for the dialog, that can be fired by the user with the Delete button, to remove and 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 DELETE block typically opens a message box to let the user confirm the row deletion.

The int_flag register is used to validate or cancel the row deletion, when the control goes back to the DISPLAY ARRAY dialog. An SQL DELETE instruction must be executed to remove the row from the database table, by using the primary key value of the current row. If the DELETE fails, set the int_flag to FALSE in order to cancel the row deletion from the record list controlled by the DISPLAY ARRAY.

ON DELETE code example:
  1      ON DELETE
  2         LET x = DIALOG.getCurrentRow("sa_cust")
  3         LET int_flag = NOT comutils.mbox_yn("Customers",
  4                               "Do you want to delete the current row?")
  5         IF NOT int_flag THEN  
  6             TRY
  7                 DELETE FROM customer WHERE cust_num = custarr[x].cust_num
  8             CATCH
  9                 ERROR SQLERRMESSAGE
 10                 LET int_flag = TRUE
 11             END TRY 
 12         END IF
Note:
  • Line 1 declares the ON DELETE trigger.
  • Line 2 assigns the current row index to the x variable defined as INTEGER.
  • Lines 3 and 4 use the mbox_yn utility function from the comutils module, to ask confirmation to the user.
  • Line 5 checks the int_flag register.
  • Line 6 thru 11: here we try to DELETE the customer record in the SQL table. This SQL statement may fail, because of some SQL table constraints (FOREIGN KEY). 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 deletion in the record list.
  • Line 7 implements the SQL DELETE statement, using the customer number of the current row of the dynamic array: custarr[x].cust_num.