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 body of the trigger is written in the SQL procedural language, SQL PL.

The statements specified in the SQL trigger are executed each time an 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. If an object does not exist when the trigger is being created, dynamic SQL can be used to generate a statement that references the object. 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.