In contrast to DB procedures which must be called explicitly from an application programming language, triggers are implicitly started after executing an INSERT, UPDATE, or DELETE statement on a base table.
If a trigger is defined for a base table and this trigger is to be started for each INSERT, for example, then the actions determined within this trigger are automatically executed whenever a new entry is made to that table. Preliminary conditions can be defined for the execution of a trigger. UPDATE triggers can be formulated only for the update of individual columns.
A useful case of application for triggers is to check, in addition to the domain definition of a value to be inserted, whether this value is appropriate for the entry. Any complex checks can be started in background, even related to other tables.
Triggers can also incite derived database modifications for one or another row; they can even contain complicated rules for access controls.
Before performing an UPDATE or DELETE, the old value can be saved into another table for statistical purposes. They can be processed there at a later point in time.
These examples illustrate that both the new as well as the old value can be accessed when programming a trigger. This is denoted by the keywords NEW and OLD in the trigger program.
Triggers are defined in two steps by using the programming language SQL-PL.
First, the actions to be started when calling a trigger are defined in an SQL-PL program. Such a program is denoted by the keyword TRIGGER. The control structures provided by SQL-PL can be used for this purpose; the same constraints apply that are valid for DB procedures.
Second, the trigger must be associated with a base table and with individual columns, if desired, as well as with an action, such as INSERT, UPDATE, or DELETE. Column values are passed to the trigger by using defined formal parameters.
An input menu is offered by the workbench for the definition of formal parameters. This menu prompts for the required specifications. Conditions can be specified there that restrict the execution of a trigger. For example, it can be determined that the trigger is only to be started when the input value is greater than a given value.
To be able to define a trigger for a table, one must be the owner of the table and must have the right to execute actions defined within a trigger. The same rules for access control that are valid for DB procedures are valid for the triggers.
If the execution of a trigger fails, the corresponding INSERT, UPDATE, or DELETE statement is also cancelled by a rollback.
A trigger can call other triggers implicitly and DB procedures explicitly. Like a DB procedure, a trigger is directly performed in the address space of the database kernel. This has the advantage that communication overhead is saved, especially in client server configurations.