Creating a trigger

To create a trigger:

1. Open the Create Trigger notebook.
2. In the Trigger page, specify the trigger's properties.

Important: The selections you make in this page determine the available controls in the next page.

3. In the Triggered Action page, specify the triggered action properties.
4. Click OK. OK is available only when you provide all of the required settings and type characters in the Triggered action field.

Related information

• Viewing a trigger definition
• Adding, modifying, or removing a comment on a trigger

Specifying the trigger's properties

1. Specify the trigger's identifying information in the fields:

Trigger schema

Optional: Type a short SQL identifier. Do not begin the schema name with the string 'SYS' unless the name is SYSADM. If you do not specify a schema name, DB2 qualifies the trigger name with an implicit schema name.

Trigger name

Specify the name of the trigger. DB2 implicitly or explicitly qualifies this value with a schema. The name, including the qualifying schema, must not identify an existing trigger or trigger package at the current server.

Table owner

Specify the name of a authorization ID that owns the table specified in the Table name field.

Tip: To display a list of existing objects, click on the (Browse) button next to the field. Select an existing object from the list.

Table name

Specify the name of a base table at the current server. The action that you specify as the "Operation that causes the trigger to be executed" occurs on this table, the triggered action executes.

Important: Do not type the name of a temporary table, an alias, a synonym, or a catalog table.

Tip: To display a list of existing objects, click on the (Browse) button next to the field. Select an existing object from the list.

2. In the Time to trigger action box, select a radio button to specify the type of trigger:

Before

Defines this trigger as a before trigger. DB2 executes the triggered action before applying any changes caused by the triggering SQL statement.

After

Defines this trigger as an after trigger. DB2 executes the triggered action after applying any changes caused by the triggering SQL statement.

3. In the Operation that causes the trigger to be executed box, specify the operation:
• Insert
• Delete
• Update of any column
• Update of column
Selecting the Update of column radio button causes DB2 to populate the Available columns list box.


If you select the Update of column radio button, then you must select one or more columns from the Available columns list box. Use the > and >> (Arrow) push buttons to move Available columns to the Selected columns list box.

4. Optional: Add a comment to the trigger by typing the comment in the Comment field.

Specifying the triggered action properties

1. On the Triggered Action page, enter the trigger's referencing information in the text fields. This information specifies the correlation names of the rows and temporary tables for the trigger's associated table. DB2 uses these names and temporary tables to store the previous state of the data in the triggered row or table data.

Important: All the correlation names and temporary table names must be unique from one another.

Correlation name for the old rows

Type a name to identify the state of the row prior to the triggering SQL operation.

This field is available if you select the Delete radio button or either Update radio button in the Trigger page.

Correlation name for the new rows

Type a name to identify the state of the row as modified by any SET statement in any before triggers that have already been activated by the triggering SQL operation.

If you select the After radio button and the Insert or either Update radio button in the Trigger page, this field is available.

Temporary table for the old rows

Type a name to identify the temporary table that stores the state of the entire set of rows prior to the application of changes from the triggering operation.

This field is available only if you select the After radio button and the Delete or either Update radio button in the Trigger page.

Temporary table for the new rows

Type a name to identify the temporary table that stores the state of the entire set of rows as modified by any SET statement in any before triggers that have already been activated by the triggering SQL operation.

This field is available only if you select the After radio button and the Insert or either Update radio button in the Trigger page.

2. Specify a behavior for the triggered action by selecting a radio button:

Row

Specifies that the triggered action occurs every time the triggering operation acts on a row in the associated table.

Statement

Specifies that the action occurs one time after the operation acts on all rows. This radio button is available only if you select the After radio button in the Trigger page.

3. Type the triggered SQL statements in the Triggered action field field. DB2 executes the SQL statements in the order that they are entered.