CREATE TRIGGER Statement (create_trigger_statement) 

The CREATE TRIGGER statement (create_trigger_statement) defines a trigger for a base table.

Syntax

<create_trigger_statement> ::= CREATE TRIGGER <trigger_name> FOR <table_name>
AFTER <trigger_event>,... EXECUTE (<routine>) [WHENEVER <search_condition>]

<trigger_event>            ::= INSERT
                             | UPDATE [(<column_list>)]
                             | DELETE
<column_list>              ::= <column_name> | <column_list>,<column_name>

Examples

SQL Tutorial Database Triggers

Explanation

A trigger is a special type of database procedure that is assigned to a base table. A trigger cannot be executed explicitly. Instead, it is executed automatically by the database system when defined events trigger_event occur in the table.

If a schema is not specified in the table name, the current schema is accepted implicitly.

The table name must identify an existing table in the schema. The current user must be the owner of the specified table.

<trigger_event>

The  trigger_event defines which event sets off the trigger. This trigger is called whenever the triggering event takes place, as long as no faults occur.

INSERT: The INSERT trigger event causes the trigger to be executed whenever a row is inserted in the table.

UPDATE: The UPDATE event causes the trigger to be executed whenever a row in the table is modified. If a  column_list  is specified, the trigger is only called if one of the columns in the column list is modified.

DELETE: The DELETE trigger event causes the trigger to be executed whenever a row is deleted from the table.

A maximum of one trigger may be defined for each trigger event in each table.

For an INSERT, UPDATE or DELETE statement you can use IGNORE TRIGGER so that the trigger event INSERT, UPDATE or DELETE is ignored.

<routine>

Please note the following information regarding the trigger routine.

Each INSERT trigger implicitly has the corresponding variable NEW.<column_name> for each column in the table. When the trigger is executed, this variable has the value of the corresponding column in the inserted row. It is only permissible to specify NEW for SQL statements specified in routine_sql_statements. Specifying NEW for other statements causes an error to occur.

Each UPDATE trigger implicitly has a corresponding variable NEW.<column name> and OLD.<column name> for each column in the table. When the trigger is executed, the OLD.<column_name> variable has the value of the corresponding column in front of and NEW.<column_name> after the row is changed. Specifying NEW and OLD is optional.

Each DELETE trigger implicitly has a corresponding variable OLD.<column_name> for each column in the table. When the trigger is executed, this variable has the value of the corresponding column in the deleted row. You can only specify OLD for SQL statements specified in routine_sql_statements. Specifying OLD for other statements causes an error to occur.

A colon must always be used with NEW and :OLD in SQL statements  statement  used in triggers specified as routine_sql_statements
(For example: UPDATE hotel.room SET hno = :NEW.hno WHERE hno = :OLD.hno).

In SQL statements used in triggers, which are not routine_sql_statements  NEW and OLD must always be used without a colon (For example: IF NEW.hno <> OLD.hno).

If, on STOP, the trigger ends an error number other than zero, the entire SQL statement that set off the trigger fails.

The SUBTRANS statement is not allowed in a trigger.

If a WHENEVER statement is specified, the trigger is only executed as long as the search condition is fulfilled. The condition cannot contain a subquery nor any set functions.

See also:

CREATE SYSTEM TRIGGER Statement

CREATE DBPROC[EDURE] Statement (create_dbproc_statement)