Triggers are a mechanism that allows you to perform an action when someone modifies a table. The trigger mechanism in OpenBase allows you to perform different actions depending on whether the table is modified with an insert, update or delete. Triggers allow both modified and original values to be passed in.
Video: OpenBase TriggersHere is the syntax for creating a trigger using ALTER TABLE:
alter table <tablename> trigger <triggername> <triggerstring> on UPDATE INSERT DELETE
Values from the record that is triggers the trigger can be passed into the trigger definition. This is done by placing the & or && before labels that represent current and previous values. For example, if you have a column called "name", &name represents the present value and &&name represents the value before the record was modified.
Here is an example of how to add a trigger using the pubs database.
Step 1. First create the table test in the pubs database:
CREATE TABLE test (name VARCHAR)
Step 2. Next create a trigger on the authors table which will insert a record into test each time it is modified. Here is how we do that:
alter table authors TRIGGER foo "insert into test (name) values (&au_fname )" on update insert delete
Then each time you do anything to the authors table it will insert the firstname into the test table. The variable &au_fname is replaced by the value for au_fname in the record.
Cascade Delete Trigger
You can also use triggers to perform a cascading delete. This is where you want records in joined tables to be automatically deleted when the parent record is deleted.
Here is an example:
ALTER TABLE authors TRIGGER CASCADE_DELETE "DELETE FROM table2 WHERE AUTHORS_ID = &_rowd " ON DELETE
This will trigger a delete in table2 when an authors record is deleted. Here is the syntax for creating a trigger using CREATE TABLE:
CREATE TABLE FOO (foo_name VARCHAR) TRIGGER FOO_RECORD "INSERT INTO TEST (NAME) VALUES (&foo_name)" ON UPDATE INSERT TRIGGER CASCADE_DELETE "DELETE FROM test WHERE NAME = &foo_name" ON DELETE
This creates two triggers on the foo table at the time of creation. The first trigger records the foo_name field in the test table each time it is modified. The second trigger creates a cascading delete when a record is deleted.
Dropping Triggers
Dropping triggers can be accomplished by an ALTER TABLE call to remove the trigger. In the example below “FOO_RECORD” is the name of the trigger.
alter table FOO remove trigger FOO_RECORD
Pitfalls to avoid
One thing to avoid is triggers that trigger themselves creating a circular loop. If a loop happens (or more than 10 levels of triggers are detected) the server will automatically stop the transaction and issue a rollback. If you want to perform a calculation, using derived columns is the way to do it. If you must use a trigger to update a record that triggers the trigger, you can use a timestamp field to ensure that the trigger does not trigger itself indefinitely. For instance:
alter table authors TRIGGER update_self "update authors set au_fname = "Mr." + &au_fname, trigger_timestamp = NOW() where _rowid = &_rowid and trigger_timestamp != NOW() " on update insert
This trigger appends a "Mr." at the beginning of the au_fname column every time there is an update or insert. It assumes the existence of a column trigger_timestamp which is used to ensure that the update triggered by the trigger does not trigger itself more than once. Keep in mind that the NOW() function will return the exact same value for ALL commands within the same transaction.