- Creating server triggers in the analysis
- Taking the server triggers into account
- Stored procedures linked to a trigger
- Testing a stored procedure called by a trigger
- Handling the server triggers through programming
- The WLanguage functions
Available only with this kind of connection
The server triggers are stored procedures run by the server before or after a write operation performed on a database file.
As for the standard triggers, two types of server triggers are available:
- The "BEFORE" triggers:
A "BEFORE" trigger is called:
- before running an HFSQL function (HAdd, HModify, HDelete, ...).
- before running a function for managing the Table controls based on a data file.
A "BEFORE" trigger can be used for example to check the consistency of data for the items of a record. With this type of trigger, an HFSQL variable can be initialized in order to cancel the execution of the associated HFSQL function.
- The "AFTER" triggers:
An "AFTER" trigger is called:
- after running an HFSQL function (except if the program was interrupted during the execution of this function).
- after running a function for managing the Table controls based on a data file.
An "AFTER" trigger can be used to manage the process of errors for example.
Characteristics of server triggers:
- Several server triggers can be associated with the same data file.
- The triggers being defined on the server, they are taken into account regardless of the client application run. Any new trigger defined on the server is automatically taken into account.
- The server triggers are run:
Creating server triggers in the analysis
To create a server trigger:
- on the "Analysis" tab, in the "Creation" group, expand "New" and select "Trigger".
- select "New trigger" from the popup menu of the dockable "Analysis" pane.
- display the "HF triggers" tab of the data file description window and click "Create a new trigger"
These different options display the description window of the server trigger.
The description window of a trigger is used to specify:
- in the "General" tab:
- the name of the trigger.
- the stored WLanguage procedure that is associated with the trigger.
- the mode for releasing the trigger (BEFORE or AFTER the execution of the HFSQL functions).
- the HFSQL functions that will release the trigger.
- in the "General" tab:
- the data files associated with the trigger.
Remark: The created triggers are visible:
- in the analysis pane.
- in the analysis graph directly: if a server trigger is associated with data file, a specific icon is displayed: .
Taking the server triggers into account
The server triggers created in the analysis are created on the server:
The triggers installed on the server will be automatically taken into account by the client applications.
Stored procedures linked to a trigger
A "trigger" stored procedure accepts no parameter. However, some HFSQL state variables are positioned before each call:
|H.FileName||Character string: Logical name of the data file whose trigger is activated.|
|H.Action||Character initialized to "A" for a Before trigger and to "P" for an After trigger.|
|H.TriggerFunction||Character string: Name of HFSQL function that activated the trigger.|
|H.ToDo||During the execution of a before trigger:|
- cancel the execution of the HFSQL function by assigning "A" to the HFSQL state variable: H.ToDo = "A". In this case, the action is not performed and the function (HAdd, HModify, etc.) returns True (no error).
- cancel the execution of the current HFSQL function by assigning "E" to the HFSQL state variable: H.ToDo = "E". In this case, the action is not performed and the function (HAdd, HModify, etc.) returns False. The error message is as follows: "The action on XXX file was interrupted by the trigger".
- When a "BEFORE" trigger and an "AFTER" trigger are associated with an HFSQL function, if "BEFORE" cancels the execution of the HFSQL function (by setting h.ToDo to "A"), "AFTER" is not executed.
- In the code of the stored procedure, the MyFile keyword is used to identify and handle the name of the data file on which the trigger has been used.
: In order for a stored procedure started from a server trigger to access the data, you must use HDeclareExternal
. This function allows you to declare the data sources that will be used in processes of the stored procedure.
Indeed, when a stored procedure is started from the HFSQL Control Center, there is no current analysis: the HFSQL Client/Server data is not immediately accessible.
is not used in the code of the stored procedure, the procedure will trigger a fatal error, notified in the log of system events.
Testing a stored procedure called by a trigger
To run the test of a stored procedure:
- In the analysis pane, select the stored procedure whose test must be run.
- Select "Run the procedure test" from the popup menu of the stored procedure.
- The window for entering the parameters of the procedure is displayed. In this window, you can:
- Type the procedure parameters.
- Run the procedure test.
Remark: this window can be used to restart the execution several times by modifying the parameters.
- When the procedure test is run:
- An update of the stored procedure is proposed if necessary.
- The procedure is started on the server.
- The return value of procedure is displayed if necessary.
- The debugging port is port 27281 by default. This port must be opened in the firewall. This port can be modified in the HFConf.ini file.
- To run the test of a stored procedure, you must have the debugging rights on the database.
- The traces used in the stored procedures are displayed in the "Trace of debugger" pane.
- The code of stored procedure can contain breakpoints: the debugger will be started.
- The elements deployed on the server are used during the test.
- InTestMode returns True.
Handling the server triggers through programming
The WLanguage functions
Several functions are used to handle the server triggers:
These functions are advanced functions.
Training (WINDEV): WD Server trigger
[ + ]
This example explains how to use the server triggers.
The server triggers are run on the server whenever adding/modifying/deleting records.
This page is also available for…