CREATE TRIGGER (database trigger)

Use the CREATE TRIGGER command to create database triggers that fire when a modification or attempted modification to an ObjectServer table occurs (or when a modification or attempted modification to a view affects a base table).

Syntax

CREATE [ OR REPLACE ] TRIGGER trigger_name
 GROUP group_name
 [ DEBUG { TRUE | FALSE } ]
 [ ENABLED { TRUE | FALSE } ]
 PRIORITY integer
 [ COMMENT 'comment_string' ]
 { BEFORE | AFTER } { INSERT | UPDATE | DELETE | REINSERT } 
 ON database_name.table_name
 FOR EACH { ROW | STATEMENT }
 [ WHEN condition ]
 [ DECLARE variable_declaration ]
 BEGIN
   trigger_action
END;

If there is a possibility that a trigger already exists with the same name as the one that you want to create, use the optional OR REPLACE keywords. If the trigger exists, it is replaced by the one that you are creating. If the trigger does not exist, a new one is created.

The trigger_name value must be unique within the ObjectServer and comply with the ObjectServer naming conventions.

The group_name value can be any trigger group already created by using the CREATE TRIGGER GROUP command.

If DEBUG is set to TRUE, debugging information is sent to the ObjectServer message log, if the message level is set to debug.

If ENABLED is set to TRUE, the trigger fires when the associated incident occurs. Otherwise, the trigger does not fire when the incident occurs.

The PRIORITY of a trigger determines the order in which the ObjectServer fires triggers when more than one trigger is associated with the same incident. The priority can be in the range of 1 to 20. The lower the number, the higher the priority, so a trigger with a priority of 2 is fired before a trigger with a priority of 3. If more than one trigger of the same priority is fired because of the same incident, the order in which these triggers fire is undetermined.

Use the optional COMMENT keyword to add a comment (comment_string) for the trigger.

The BEFORE or AFTER timing keyword specifies whether the trigger runs before or after the database modification that caused the trigger to fire occurs. For example, you can create a BEFORE trigger that evaluates the name of the user before a row in the alerts.status table is deleted. In the trigger, you can detect whether the user is allowed to delete from the alerts.status table, and if not, prevent the database modification from taking place. With an AFTER trigger, the database modification always takes place.

The database_name.table_name is the name of the database and table affected by the trigger action.

A database trigger fires at one of the following levels:

FOR EACH ROW (known as a row-level trigger): Row-level triggers fire once for each row returned as a result of the database modification.
FOR EACH STATEMENT (known as a statement-level trigger): Statement-level triggers fire once for each database modification.

Note: Only row-level triggers can be defined to fire on inserts and reinserts.

Note: BEFORE statement-level triggers always fire before BEFORE row-level triggers, and AFTER statement-level triggers always fire after AFTER row-level triggers, regardless of trigger priority.

Use the optional WHEN clause to test for a particular condition before the trigger action runs. If the condition is not met, the trigger action does not run.

You can optionally declare local trigger variables for use in the body of the trigger. These variables are declared and used in the same way as procedure variables. However, trigger variables are static, so they maintain their value between the times when the trigger runs.

Example

A database signal is raised as a result of the following SQL statement:

DELETE FROM alerts.status WHERE Severity = 5;

When this statement runs, the ObjectServer deletes all the rows in the alerts.status table with a severity of 5. If there are 20 rows in the table with this severity and the level is set to FOR EACH ROW, 20 rows are deleted and the trigger is raised 20 times. If the level is set to FOR EACH STATEMENT, the trigger is raised once.