Syntax:
CREATE TRIGGER [IF NOT EXISTS] trigger-name [BEFORE | AFTER | INSTEAD OF] database-event ON table-name trigger-action database-event: UPDATE [OF column-name[, column-name]* | INSERT | DELETE trigger-action: [FOR EACH ROW ] [WHEN expression] BEGIN trigger-step; [trigger-step;]* END; trigger-step: update-statement | insert-statement | delete-statement | select-statement
The CREATE TRIGGER statement is used to add triggers to the database schema. Triggers are database operations (trigger-actions) that are automatically performed when a specified database event (database-event) occurs. The trigger-name must be different form table-name, view-name and index-name.
A trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a particular table occurs, or whenever an UPDATE of one or more specified columns of a table is performed.
At this time GDBase supports only FOR EACH ROW triggers. FOR EACH STATEMENT triggers are currently not supported. Hence, explicitly specifying FOR EACH ROW is optional. FOR EACH ROW implies that the SQL statements specified as trigger-steps may be executed (depending on the WHEN clause) for each database row which is inserted, updated or deleted by the statement causing the trigger to fire.
Both the WHEN clause and trigger-steps may access elements of the row being inserted, deleted or updated using references of the form NEW.column-name and OLD.column-name, where column-name is the name of the column from the table that the trigger is associated with. OLD and NEW references may only be used in triggers on database-events for which they are relevant, as follows:
| INSERT | NEW references are valid |
| UPDATE | NEW and OLD references are valid |
| DELETE | OLD references are valid |
If a WHEN clause is present the SQL statements specified as trigger-steps are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the SQL statements are executed for all rows.
An ON CONFLICT clause may be added as part of an UPDATE or INSERT trigger-step. However if an ON CONFLICT clause is specified as part of the statement causing the trigger to fire, then this conflict handling policy is used instead.
Triggers are automatically dropped when the table that they are associated with is dropped.
Triggers may not be created on views.
Example 43.12. Using a trigger to keep track of delivery addrresses.
Assuming that customer records are stored in the "customers" table, and that order records are stored in the "orders" table, the following trigger ensures that all associated orders are redirected when a customer changes his or her address:
CREATE TRIGGER update_customer_address
UPDATE OF address ON customers
BEGIN
UPDATE orders
SET address = new.address
WHERE customer_name = old.name;
END;
With this trigger installed, executing the statement:
UPDATE customers
SET address = '1 Main St.'
WHERE name = 'Jack Jones';
causes the following code to be automatically executed:
UPDATE orders
SET address = '1 Main St.'
WHERE customer_name = 'Jack Jones';
Note that currently triggers may behave oddly when created on tables with INTEGER PRIMARY KEY fields. If a BEFORE trigger program modifies the INTEGER PRIMARY KEY field of a row that will be subsequently updated by the statement that causes the trigger to fire, then the update may not occur. The workaround is to declare the table with a PRIMARY KEY column instead of an INTEGER PRIMARY KEY column.
A special SQL function RAISE() may be used within a trigger program.
Syntax:
RAISE (ABORT, error-message) | RAISE (FAIL, error-message) | RAISE (ROLLBACK, error-message) | RAISE (IGNORE)
When one of the first three forms is called during trigger-program execution, the specified ON CONFLICT processing is performed (ABORT, FAIL or ROLLBACK) respectively and the current query terminates. An error message is returned to the user.
When RAISE(IGNORE) is called, the remainder of the current trigger program, the statement that caused the trigger program to execute and any subsequent trigger programs that would have been executed are abandoned. No database changes are rolled back. If the statement that caused the trigger program to execute is itself part of the trigger program, then that trigger program resumes execution at the beginning of the next step.
Triggers are removed using the DROP TRIGGER statement.