CREATE TRIGGER Statement (create_trigger_statement)

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

Structure

Syntax 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>

End of the code.

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 database 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 for each change made to a row in the table. 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. You can only 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 before the row is changed, and the NEW.<column_name> variable has the value of the corresponding column after the row is changed. Specifying NEW and OLD is optional.

Each DELETE trigger implicitly has the 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.

Caution Caution

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

A colon may never be used with NEW and OLD in SQL statements that are used in triggers and are not specified in routine_sql_statements (For example: IF NEW.hno <> OLD.hno).

End of the caution.

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 or any set functions.

More Information

CREATE SYSTEM TRIGGER Statement

CREATE DBPROC[EDURE] Statement (create_dbproc_statement)