Defining Flex SQL statements

Define Flex SQL statements to define validation rules that are specific to your organization. You can set up one or more statements to be processed for post-insert or post-update events.

Flex SQL processing supports data query (select) and data manipulation (insert, update, delete) statements. Use select statements to perform a check condition.

Uppercase and lowercase characters are allowed in the SQL statement. The row identifier token, however, must be either all uppercase or all lowercase (i.e., :ROWID or :rowid).

Every Flex SQL statement requires the use of a predefined :ROWID token. This token refers to the database row identifier for the record being processed in the specified table. The statement is executed for each record in the table affected by the insert or update operation.

The maximum statement length is 4000 characters. No statement termination character (;) is required.

Data manipulation statements are allowed, but make sure you do not begin an infinite cascading of trigger steps. For example, generally it is not recommended to create a Flex SQL statement that updates the base table identified in Table.

Note: Infor EAM recommends that you define Flex SQL procedures in close cooperation with your Infor EAM consultant.

To define Flex SQL statements:

  1. Open the Flex SQL form.
  2. Click New Record.
  3. Table—Enter the database table.
  4. Sequence Number—Enter the sequence number for the Flex SQL statement.
  5. Trigger—Select one of the following options:
    • Post Insert
    • Post Update
  6. SQL Statement—Enter the SQL statement for the selected database table.
  7. Failure Message—Enter the failure message.
  8. Comments—Enter comments regarding the Flex SQL statement.
  9. Must Exist—Select if the Flex SQL statement must exist in the database table. If Must Exist is selected, the system runs SQL statements in order of Sequence Number. If one statement fails, the remainder do not run. If Must Exist is not selected, each statement runs separately.
  10. Abort on Failure—Select for the system to abort the statement upon failure. If Abort on Failure is checked, the system returns the Failure Message field value, does not process any remaining SQL statements, and cancels the save that triggered the flex SQL.
  11. Reverse Return Code—Select for the system to automatically reverse the returned value upon statement completion, i.e., Null becomes Not Null.
    Reverse Return Code is used only with Abort on Failure.
  12. Active—Select to set this Flex SQL statement record to active.
  13. Click Save Record.
    Note: Click Test Flex SQL to check the validity of the SQL statement.