The Triggers page is used to set a list of table triggers.
A trigger is a function consisting of SQL commands and describing the operations to be executed under certain conditions.
To create a trigger, click the Add button. A line is added to the end of the list, in which specify the following attributes:
Name. Enter trigger name. The default name is Trigger.
Identifier. Enter trigger’s unique identifier (only Latin letters, numbers, and underscores are allowed; spaces are not allowed). The default identifier: <table identifier>_TRIGGER<number>.
Execution Condition. Determine the condition, at which calculation is executed. Set event execution time by selecting the required radio button:
AFTER. After adding, deleting, or modifying data.
BEFORE. Before adding, deleting, or modifying data.
Then select the trigger action event by selecting the required checkboxes:
INSERT. Adding data to the table.
DELETE. Deleting data from the table.
UPDATE. Modifying data in the table.
For Each Row. If required, deselect the checkbox of the trigger, which will be executed once for the entire changed set of records. The checkbox is selected by default, the trigger is executed once for each changed record.
NOTE. The attribute is used only when script text is specified in the Script box for Oracle, Teradata, PostgresSQL drivers.
Text. Specify trigger script text in the SQL language.
To edit the selected trigger, change value in the corresponding column.
To change the number of displayed attributes, click the Settings button and select or deselect the checkbox next to attribute name.
To delete one or several triggers, select them and click the Delete button. A confirmation dialog box opens.
To finish the wizard, click the Save button.
To specify trigger script text, click the Script button in the field of the Text attribute.
After executing the operation the Script dialog box opens:
In the Script Text field specify trigger code in the SQL language. The code is entered according to the SQL syntax, which is set for the selected DBMS driver. For example, code should be specified between the keywords for Microsoft SQL Server based servers:
AS
BEGIN
...
END;
Code should be specified between the keywords for Oracle based servers:
BEGIN
...
END;
The selected DBMS driver and the driver list are displayed next to the Script Text field. By default, the driver list displays the selected driver used during repository connection setup. If script text is specified for one or several drivers, the selected driver(s) will be marked in the driver list.
To show or hide the driver list, use the Hide Driver List/Show Driver List button.
To set the specified script text for all DBMS, click the Apply for All DBMS button. The confirmation dialog box opens. If script text is specified for at least one driver that is different from the current one, all the specified scripts will be replaced with the current one if the answer is positive.
To apply trigger script texts to the selected DBMS drivers, click the Apply button.
To create a trigger:
Click the Add button.
Double-click next to the trigger list.
The Trigger Properties dialog box opens.
To edit selected trigger:
Click the Edit button.
Double-click the trigger.
The Trigger Properties dialog box opens. Enter required changes to it. The Trigger Properties dialog box also opens on double-click.
To delete the current trigger, click the Delete button or press CTRL+DELETE.
If a trigger is created outside Foresight Analytics Platform (for example, in TOAD), it can be refreshed. To do this, click the Refresh button.
The Trigger Properties dialog box opens when a trigger is created or edited:
Determine the following attributes in the opened dialog box:
Name. Enter the trigger's name.
Identifier. The trigger’s unique identifier (only Latin letters, numbers, and underscores can be used; spaces are not allowed).
Execution Conditions. Define the condition, under which the calculation is executed: first you need to define the time of executing one or several selected events. To do this, select appropriate radio buttons:
AFTER. After adding, deleting, or modifying data.
BEFORE. Before adding, deleting, or modifying data.
Then select the trigger action event; to do this, checkmark appropriate boxes:
INSERT. Adding data to the table.
DELETE. Deleting data from the table.
UPDATE. Modifying data in the table.
NOTE. If a Microsoft SQL Server driver is chosen, the AFTER/BEFORE checkbox does not affect the created trigger, because Microsoft SQL Server does not have such division.
Execute for Each Row. This checkbox is relevant if Oracle drivers are selected. This is explained by the Oracle drivers feature which enables the user to set up triggers executed only once for each modified record (for each row) or triggers executed once for the entire modified set of records (without "for each row").
Driver. Select the driver type in the drop-down list. For details about available drivers see the Supported DBMS section. A driver corresponding to table database is highlighted in bold. It is selected by default on dialog box opening. One can determine a specific text for each driver, which is the trigger text, entered into corresponding field, must be linked to the selected database type. Start conditions do not depend on the driver. When the Execution Conditions checkbox is deselected, the Driver list and the Apply to All DBMS button are still available.
Text. Enter trigger code in the SQL language in this field. The code is entered according to the SQL syntax, which is set up for the selected DBMS. For example, the entire code must be specified between keywords for servers based on Microsoft SQL Server:
AS
BEGIN
...
END;
The entire code must be specified between keywords for Oracle-based servers:
BEGIN
...
END;
Apply to All DBMS. This command enables the user to set the selected text for all DBMS. If at least one driver except the current one has non-empty trigger text, clicking this button opens a confirmation dialog box. Selecting Yes sets the displayed trigger text for all DBMS, that means that all currently existing trigger texts are replaced with the current one. If triggers' text is empty for all drivers except the current one, no confirmation dialog box is not displayed.
See also: