This chapter discusses database triggers: how to create them, execute them and drop them.
A trigger defines an SQL statement that is automatically executed before, after, or instead of a specified data manipulation operation on a particular table or view.
A trigger can either be a statement trigger which means that the trigger is executed once for a data manipulation statement, or a row trigger which means that the trigger is executed once for each row affected by the data manipulation statement causing the trigger.
The execution of the SQL statement can be made conditional on the evaluation of a search condition.
The SQL statement in the trigger definition is typically a compound SQL statement, thus allowing a number of SQL statements to be executed by the trigger. The compound SQL statement must be defined as ATOMIC. Thus, the body of a trigger is similar to the body of a routine and the same language constructs may be used within it. In this code it is possible to refer to the data that was affected by the data manipulation statement which caused the trigger to be executed.
In a statement trigger the affected data is stored in temporary tables. The data in these tables can only be read and not modified. Depending on which event that causes the trigger there can one or two tables. For delete there is an old table containing all rows that are deleted. For insert there is a new table containing all inserted rows. An update trigger will have both an old and a new table. The old table contains the rows as they were before the update took place while the new table contains the rows as they are after the update has taken place.
In order to be able to refer to these temporary tables, the trigger definition must contain a referencing clause which identifies which names that are used when referencing these tables in any DML statement within the trigger body. The old and new table will have the same layout as the table on which the trigger is defined. An example can be seen below.
A row trigger, depending on the event, will have old row and new row variables that can be referred to in the trigger code. These row variables will have fields with the same name and data type as the columns in the table on which the trigger is defined. For instance, if a column is defined as a user-defined type, it is possible to use any method defined for that type for the corresponding field.
A delete trigger will have an old row variable that contains the deleted row. An insert will have a new row variable that contains the inserted data. An update trigger will have both an old row and a new row variable. Individual data items in these variables are referenced by using dot notation. (See the row trigger example below where
o.country_codeis used to refer to data for the deleted row.) The old row variable is read only but the new row variable can be modified in a before trigger (except that columns defined as large objects are read only in this version of Mimer SQL.)
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40