Enterprise Manager Java App: Triggers

Triggers are a powerful feature of the BBj SQL file system that allows developers to execute BBj code when certain operations occur on a file. Triggers can be useful for:

validating data before writing it to a file
validating permissions before allowing a user to read or write to a file
performing data manipulation before writing it to a file or database table
and more

Developers can use the Enterprise Manager to create and modify triggers using a handy graphical interface or using the CREATE TRIGGER statement to manage file triggers programmatically via SQL.

Configuring and Managing Triggers

Triggers in BBj are at the file level. This means that triggers will get fired regardless of whether the operation on the file is a READ RECORD coming from a BBj program, or from an SQL operation by a third party ODBC/JDBC application.

To configure triggers in the Enterprise Manager, first select the "Triggers" item from the navigator. The following panel appears in the right pane:

The [Triggers] tab shows a list of the directories for which the administrator has chosen to show triggers. To be able to access triggers on files in a particular directory, first add a directory using the [MountDirectory] button.

After mounting the appropriate directory, add a trigger as follows:

Click the green [+] at the bottom of the panel.
Select the type of trigger from the left side list (i.e. Before Write, After Read, Instead of Remove, etc.).
Put a check in the "Enabled" box to enable the trigger.
Specify the location of the BBj source code file to execute when the trigger operation occurs.

NOTE: The source code viewer in the Enterprise Manager is read-only. BASIS advises using the BASIS IDE to edit trigger source code files to gain the benefit of syntax highlighting and other IDE features.

To make changes to a trigger, select the trigger from the list, and click [Edit Trigger].

The trigger editor provides a quick way to manage the various types of triggers on a particular file. The following describes each of the settings:

Trigger Level Properties

Use The triggers On This File	Enable or disable all triggers for the data file
Config File	Location of a specific config.bbx file to use for this particular set of triggers. Leave blank to use the default config.bbx.
Working Directory	Working directory for the running interpreter used by the trigger to execute the trigger code.
Run As User	User account to use for running the interpreter used for executing the trigger code. Leave blank to use the default user.
Password	Password for the" run as user."
Confirm	Confirm the" run as user" password.
Trigger Types	Displays the properties in the right side panel for the selected type. See full explanation in Trigger Types below. Types shown in green are currently enabled. Those in red are currently disabled.

Trigger Operation Properties

Enabled	Enable or disable the specific type of trigger for the file. If a specific type is enabled and misconfigured in some way, the file will not be accessible.
Data File	Shows the data file the trigger belongs to. This property is read-only.
File Name	Location of the BBj source code file that will be executed when the trigger is fired. Specify a full path to the file or a path relative to the data file the trigger belongs to. It is highly recommended that a full path NOT be used as this makes it difficult to manage.
BBj Code	Shows the current source code located in the file specified in File Name. This field is not editable and it is recommended that editing be done using the BASIS IDE.

Trigger Types

There are a variety of different types of triggers that fire under different circumstances and at different times during the data access process. There are two parts to each type of trigger: the time it fires, and the file operation that causes it to fire.

Timing Types

Each trigger fires at a particular point in the process of performing a particular file operation. The following describes the three times a trigger may fire:

Before	A before trigger is executed BEFORE the actual file operation occurs. This is useful, for example, when the data needs to validated before allowing a write.
After	An after trigger is executed AFTER the actual file operation occurs. After triggers have access to the data that was accessed during the actual file operation and can manipulate this data before it is returned to the client. One useful example would be an after read trigger that will decrypt data stored in the table before returning the data.
Instead Of	Instead of triggers are executed INSTEAD OF the actual file operation. These are useful if operations on that file should perform another operation such as get the data from a third party DBMS.

Operation Types

Most operations possible on data files can also have triggers attached to them. The following describes the different operations that can occur on a file that can have triggers attached to them:

Read	Read triggers occur when a read operation is performed on a file. This includes SQL fetch operations, BBj READ and READ RECORD calls, SELECT verb calls, and EXTRACT calls.
Key	Key triggers occur when one of the BBj language functions KEY, KEYF, KEYL, KEYN, or KEYP are called.
Write	Write triggers occur when a write operation of any kind occurs. This includes SQL UPDATE and INSERT operations, and BBj WRITE and WRITE RECORD calls.
Remove	Remove triggers occur when a record is removed from a file. These include SQL DELETE operations as well as BBj REMOVE calls.
Erase	Erase triggers occur when a file is erased. These include SQL DROP table operations and BBj ERASE calls.
Open	Open triggers occur when a file is opened. These occur any time the file is opened regardless of whether it was from an SQL statement or a BBj OPEN call.
Close	Close triggers occur when a file is closed. These will occur when SQL operations are finished with a file, and when the BBj CLOSE call is executed.