SQL triggers

 

The SQL CREATE TRIGGER statement provides a way for the database management system to actively control, monitor, and manage a group of tables and views whenever an insert, an update, or a delete operation is performed.

The statements specified in the SQL trigger are executed each time an SQL insert, update, or delete operation is performed. An SQL trigger may call stored procedures or user-defined functions to perform additional processing when the trigger is executed.

Unlike stored procedures, an SQL trigger cannot be directly called from an application. Instead, an SQL trigger is invoked by the database management system on the execution of a triggering insert, update, or delete operation. The definition of the SQL trigger is stored in the database management system and is invoked by the database management system when the SQL table or view that the trigger is defined on, is modified.

An SQL trigger can be created by specifying the CREATE TRIGGER SQL statement. All objects referred to in the CREATE TRIGGER statement (such as tables and functions) must exist; otherwise, the trigger will not be created. The statements in the routine-body of the SQL trigger are transformed by SQL into a program (*PGM) object. The program is created in the schema specified by the trigger name qualifier. The specified trigger is registered in the SYSTRIGGERS, SYSTRIGDEP, SYSTRIGCOL, and SYSTRIGUPD SQL catalogs.

• BEFORE SQL triggers
  BEFORE triggers cannot change tables, but they can be used to verify input column values and to change column values that are inserted or updated in a table.

• AFTER SQL triggers
  An after trigger runs after the corresponding insert, update, or delete changes are applied to the table.

• INSTEAD OF SQL triggers
  An INSTEAD OF trigger is an SQL trigger that is processed “instead of” an SQL UPDATE, DELETE or INSERT statement. Unlike SQL BEFORE and AFTER triggers, an INSTEAD OF trigger can be defined only on a view, not a table.

• Handlers in SQL triggers
  A handler in an SQL trigger gives the SQL trigger the ability to recover from an error or log information about an error that has occurred while processing the SQL statements in the trigger routine body.

• SQL trigger transition tables
  An SQL trigger might need to refer to all of the affected rows for an SQL insert, update, or delete operation. For example, a trigger needs to apply aggregate functions, such as MIN or MAX, to a specific column of the affected rows. The OLD_TABLE and NEW_TABLE transition tables can be used for this purpose.

 

Parent topic:

Triggers

 

Related concepts

Debugging an SQL routine

 

Related reference

SQL control statements
CREATE TRIGGER