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
- 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.
- Click Add New Action.
The Add New Action window opens.
- Select Modify Data in the actions
tree and click Next to open the Modify Data action parameters page.
- 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.
- 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.
- If you want the scene updated automatically after the Modify
Data action is implemented, select the Refresh display
on completion check box.
- Click Next to set the action parameters
depending on the selected operation type:
- If you select Insert:
On the Value parameters for the Insert operation page of the
wizard, select the columns into which you want to insert data.
Enter the
expressions to specify the values for the columns by using exact data
or calculated expressions.
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:
On the Value parameters for the Update operation page of the wizard, select the columns in which you want to update
data.
Enter the
expressions to specify the values for the columns by using exact data
or calculated expressions.
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.
On the Condition parameters for the Update operation page, click Add Row Condition to enter a new condition.
In the Expression column, select an item from the list or enter
the expression manually.
In the Operation column, enter an operator or select it from
the list.
In the Condition column, specify a value manually or by using
the expression designer.
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.
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:
On the Condition parameters for the Delete operation page, click Add Row Condition to enter a
new condition.
In the Expression column, select an item from the list or enter
the expression manually.
In the Operation column, enter an operator or select it from
the list.
In the Condition column, specify a value manually or by using
the expression designer.
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.
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.
- Click Finish to close the Add New Action wizard.
- 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)