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 postinsert or postupdate 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 recommends that you define Flex SQL procedures in close cooperation with your Infor consultant.

To define Flex SQL statements:

  1. Select Administration > Setup > Flex SQL.
  2. On the Record View tab, click New Record.
  3. Specify this information:
    Table
    Enter the database table.
    Sequence Number
    Enter the sequence number for the Flex SQL statement.
    Trigger
    Select one of the following options:
    • Post Insert
    • Post Update
    SQL Statement
    Enter the SQL statement for the selected database table.
    Failure Message
    Enter the failure message.
    Comments
    Enter comments regarding the Flex SQL statement.
    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.

    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.

    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.

    Active
    Select to set this Flex SQL statement record to active.
  4. Click Save.
    Note: Click Test Flex SQL to check the validity of the SQL statement.

    To open the Object Browser, click Launch Object Browser.