|
In this article Barry Mavin, CEO and Chief Software Architect for Recital,
details how to work with Triggers in the Recital Database Server.
Overview
A trigger is a special kind of stored procedure that runs when
you modify data in a specified table using one or more of the data
modification operations: UPDATE, INSERT, or DELETE.
Triggers can query other tables and can include complex SQL statements.
They are primarily useful for enforcing complex business rules or
requirements. For example, you can control whether to allow a new
order to be inserted based on a customer's current account status.
Triggers are also useful for enforcing referential and data integrity.
Triggers can be used with any data source that is handled natively
by the Recital Database Engine. This includes Recital, FoxPro, FoxBASE,
Clipper, dBase, CISAM, and RMS data,
Creating and Editing Triggers
To create a new Trigger, right-click the Procedures
node in the Databases tree of the Project
Explorer and choose Create. To modify
an existing Trigger select the Trigger in the Databases Tree in
the Project Explorer by double-clicking on it, or select
Modify from the context menu. By convertion we
recommend that you name your Stored Procedures beginning with "sp_xxx_",
user-defined functions with "f_xxx_", and Triggers with
"dt_xxx_", where xxx is the name of the
table that they are associated with.
Associating Triggers with a Table
Once you have written your Triggers as detailed above you can associate
them with the operations performed on a Table by selecting the Table
tab.
The Tables tab allows you to select a Trigger
procedure by clicking on the small button at the right
of the Text field.
Types of Triggers
As can be seen from the Tables tab detailed below,
The Recital Database Server handles 6 distinct types of Triggers.
Open Trigger
The Open Trigger is called after is a table is
opened but before any operations are performed on it. You can use
this trigger to record a log of table usage or provide a programmable
means of checing security. If the Trigger procedure returns .F.
(false), then the table is not opened. You can use a TRY...CATCH
block around the associated command to inform the user.
Close Trigger
The Close Trigger is called just prior to a table
being closed. In this trigger you may find it useful to get transaction
counts by using the IOSTATS() built-in 4GL function,
and record these values in a transaction log.
Update Trigger
The Update Trigger is called prior to a record
update operation being performed. You can use this trigger to perform
complex application or data specific validation. If the Trigger
procedure returns .F. (false), then the record
is not updated. You can use inform the user from within the Trigger
procedure the reason that the data cannot be updated.
Delete Trigger
The Delete Trigger is called prior to a record
delete operation being performed. You can use this trigger to perform
complex application or data specific validation such as cross-table
lookups e.g. attempting to delete a customer recortd when there
are still open orders for that specific customer. If the Trigger
procedure returns .F. (false), then the record
is not deleted.
Insert Trigger
The Insert Trigger is called prior to a record
insert (append) operation being performed. You can use this trigger
to perform such tasks as setting up default values of columns within
the record. If the Trigger procedure returns .F. (false),
then the record is not inserted.
Rollback Trigger
The RollbackTrigger is called prior to a rollback
operation being performed from within a form. If the Trigger procedure
returns .F. (false), then the record is not rolled
back to its original state.
Testing the Trigger
To test run the Trigger, select the Trigger in the Databases Tree
in the Project Explorer by double-clicking on it. Once
the Database Administrator is displayed, click the Run
button to run the Trigger.
|
