SQL Triggers

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.

videoicon3.png Video: OpenBase Triggers

Here 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.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License