Adding a Modify Data event action

Use the Modify Data event action to insert, update, or delete data in the database. This event action allows you to modify data without writing SQL statements manually.

About this task

To add a Modify Data event action, perform the following steps:

Procedure

  1. Select the object in the editor window. In the Events view, double-click the event that you want to trigger the Modify Data event action.
    The [objectname] Behavior window opens.
  2. Click Add New Action.
    The Add New Action window opens.
  3. Select Modify Data in the actions tree and click Next to open the Modify Data action parameters page.
  4. From the Operation Type area, select the type of operation:
    • Insert to specify the data that you want to add as a row.
    • Update to specify the data that you want to update under specified conditions.
    • Delete to specify the data that you want to delete as a row under specified conditions.
  5. From the Select table tree, select the table in which you want to modify data:
    • Expand All Data Sources to use a data source different from a data source that is displayed in Connections in Project Explorer. The data source from which you select the table is added to Connections automatically.
    • Expand one of the connections that are already displayed in Connections to use a data source from an existing connection.
  6. If you want the scene updated automatically after the Modify Data action is implemented, select the Refresh display on completion check box.
  7. Click Next to set the action parameters depending on the selected operation type:
    • If you select Insert:

      1. On the Value parameters for the Insert operation page of the wizard, select the columns into which you want to insert data.

      2. Enter the expressions to specify the values for the columns by using exact data or calculated expressions.

      3. In the lower part of the page, select Preview SQL statement with values if you want to view the SQL statement with specified object properties or results of expressions, or Preview SQL statement with expressions if you want to view the SQL statement with entered expressions.

    • If you select Update:

      1. On the Value parameters for the Update operation page of the wizard, select the columns in which you want to update data.

      2. Enter the expressions to specify the values for the columns by using exact data or calculated expressions.

      3. In the lower part of the page, select Preview SQL statement with values if you want to view the SQL statement with specified object properties or results of expressions, or Preview SQL statement with expressions if you want to view the SQL statement with entered expressions, and click Next.

      4. On the Condition parameters for the Update operation page, click Add Row Condition to enter a new condition.

      5. In the Expression column, select an item from the list or enter the expression manually.

      6. In the Operation column, enter an operator or select it from the list.

      7. In the Condition column, specify a value manually or by using the expression designer.

      8. Enter conditions in one column and separate rows to use the And connector, or in separate columns and one row to use the Or connector.

      9. In the lower part of the page, select Preview SQL statement with values if you want to view the SQL statement with specified object properties or results of expressions, or Preview SQL statement with expressions if you want to view the SQL statement with entered expressions.
        Restriction: All conditions in SQL statements are evaluated one by one in direct order. Changing the precedence by grouping conditions is not supported.
    • If you select Delete:

      1. On the Condition parameters for the Delete operation page, click Add Row Condition to enter a new condition.

      2. In the Expression column, select an item from the list or enter the expression manually.

      3. In the Operation column, enter an operator or select it from the list.

      4. In the Condition column, specify a value manually or by using the expression designer.

      5. Enter conditions in one column and separate rows to use the And connector, or in separate columns and one row to use the Or connector.

      6. In the lower part of the page, select Preview SQL statement with values if you want to view the SQL statement with specified object properties or results of expressions, or Preview SQL statement with expressions if you want to view the SQL statement with entered expressions.
        Restriction: All conditions in SQL statements are evaluated one by one in direct order. Changing the precedence by grouping conditions is not supported.
  8. Click Finish to close the Add New Action wizard.
  9. Click OK to close the [objectname] Behavior window.

Example

You work with table PRODUCTIONBYREGION (rsbi:/Data Sources/Sample Data Source/Database/Tables/DEMO/PRODUCTIONBYREGION). You want to add an event action that deletes a row if a value in the PRODUCT column is "PLYWOOD" and a value in the QTY column is less than 600 or if a value in the QTY column is more than 1000. Specify the condition parameters for the Delete operation as shown in the table below.
Table 1. Condition parameters for the Delete operation
Expression Operation Condition or Condition
PRODUCT = PLYWOOD =  
QTY     > 1000
QTY < 600 =  

DELETE FROM DEMO.PRODUCTIONBYREGION WHERE (PRODUCT ='PLYWOOD')
AND (QTY <600) OR (QTY >1000)