ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / WLanguage / Managing databases / HFSQL / Managing triggers
  • Overview
  • Creating server triggers in the analysis
  • Prerequisite
  • 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
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Server triggers
HFSQL Client/ServerAvailable only with this kind of connection
Overview
The server triggers are stored procedures run by the server before or after a write operation performed on a database file.
As with standard triggers, there are two types of server triggers:
  • 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

Prerequisite

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.FileNameCharacter string: Logical name of the data file whose trigger is activated.
H.ActionCharacter initialized to "A" for a Before trigger and to "P" for an After trigger.
H.TriggerFunctionCharacter string: Name of HFSQL function that activated the trigger.
H.ToDoDuring 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".

Remarks:
  • 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.
Important: 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.
If HDeclareExternal 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 test a stored procedure:
  1. In the analysis pane, select the stored procedure whose test must be run.
  2. Select "Test procedure" in the context menu of the stored procedure.
  3. 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.
  4. 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.
Notes/limitations:
  • 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 test a stored procedure, you must have 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:
HActivateServerTriggerRe-enables a server trigger that was disabled by HDeactivateServerTrigger.
HCreateServerTriggerAdds or modifies a server trigger on the HFSQL server.
HDeactivateServerTriggerDisables a HFSQL Client/Server server trigger on a server.
HDeleteServerTriggerDeletes a server trigger.
HDescribeServerTriggerAdds or modifies a server trigger.
HListServerTriggerLists the different triggers available on a connection or on one of the connection files.
HTriggerRecordBeforeRetrieves the value of the current record before the trigger(s) are executed.
These functions are advanced functions.
Related Examples:
WD Server trigger 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.
Minimum version required
  • Version 11
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 09/06/2023

Send a report | Local help