Create Trigger -- Fields and controls

Trigger page

Triggered Action page

Trigger page

Trigger schema

Trigger name

Table schema

Table name

Time to trigger action

Operation that causes the trigger to be executed

Comment

Apply

Reset

Close

Show SQL

Trigger schema

Use this box to specify the schema of the trigger that you're creating. The initial value in this box is the user ID under which you connected to the database. To specify a schema with a name that's different than this ID, use one of these methods:

• Click on the down arrow to display a list of existing schemas and select one.

• Type the name of the schema that you want. The schema can be an existing one or, if you have IMPLICIT_SCHEMA authority, it can be a new one. If it's new, its name:

  • Can contain up to 8 characters, including letters, numbers, at signs, pound signs, dollar signs, and underscores

  • Must start with a letter

  • Cannot begin with SYS

  • Cannot be one of the following reserved schema names:
    • SYSCAT
    • SYSFUN
    • SYSIBM
    • SYSSTAT

  • Can be an ordinary or delimited identifier

Trigger name

Type a name for the trigger that you're creating. The name:

• Can contain up to 18 characters
• Can be ordinary or delimited

Attention: This name must be unique within the trigger's schema. No other object in the schema can have the same name as the trigger.

Table schema

Use this box to specify the schema of the table on which you're defining a trigger. The initial value in this box is the user ID under which you connected to the database. To specify a schema with a name that's different than this ID, use one of these methods:

• Click on the down arrow to display a list of existing schemas and select one.

• Type the name of the schema that you want. The schema must already exist.

Table name

Use this box to specify the name of the table on which you're defining a trigger. Use one of these methods:

• Click on the down arrow to display of list of tables in the schema specified in the Table schema box. Select one of these tables.

• Type the name of a table that exists in the specified schema.

When you specify the table's schema and name, a list of the table's columns appears in the box below the Update of columns radio button.

Time to trigger action

Select a radio button to specify when the triggered action is to occur:

Before

The action precedes the operation that causes the trigger to be executed. You select this operation (inserting rows, deleting them, or updating them) on the Trigger page.

Attention: If you select theBefore radio button, the trigger will not cause other triggers to be activated.

After

The action follows the operation that causes the trigger to be executed. If you select the Row radio button on the Triggered Action page, the action occurs after the operation acts on each row. If you select the Statement radio button, the action occurs after the operation acts on all rows.

Operation that causes the trigger to be executed

Select a radio button to specify the type of operation that will cause the trigger to be executed:

Insert

Inserting rows into the table.

Delete

Deleting rows from the table.

Update of columns

Updating rows in one or more columns.

Use the box below this radio button to indicate which columns must be updated for the trigger to be executed. If you want specific columns to be updated, select these columns.

Tip: To select multiple columns one by one, press and hold the Ctrl key while you click on the columns. To select consecutive columns at one time, click on the first one and scroll to the last one; then press and hold the Shift key while you click on the last one.

If you don't select any columns, the trigger can be executed when any columns are updated.

If the code for the triggered action contains a search condition, the trigger will be actually executed only when columns that meet this condition are updated.

Comment

Type a comment to document the trigger that you're creating. You can type up to 254 characters.

If you don't want to add a comment now, you can do so later in the Alter Trigger notebook. If you do add a comment now, you can update or remove it later in the Alter Trigger notebook.

Triggered Action page

Correlation name for the old rows

Correlation name for the new rows

Temporary table for the old rows

Temporary table for the new rows

For each

Triggered action

Undo

Clear

Apply

Reset

Close

Show SQL

Correlation name for the old rows

Type a correlation name to refer to rows as they are before they're deleted or updated by the operation selected on the Trigger page. This field is available if, on the Trigger page, you selected the Delete radio button or the Update of columns radio button.

Correlation name for the new rows

Type a correlation name to refer to rows as they are after they're inserted or updated by the operation selected on the Trigger page. This field is available if, on the Trigger page, you selected the Insert radio button or the Update of columns radio button.

Temporary table for the old rows

Type a name to refer to a temporary table consisting of the rows affected by the operation (delete or update) selected on the Trigger page. This field is available if, on the Trigger page, you selected one of these combinations of radio buttons:

• After and Delete
• After and Update of columns

Temporary table for the new rows

Type a name to refer to a temporary table consisting of rows to be affected by the operation (insert or update) selected on the Trigger page. This field is available if, on the Trigger page, you selected one of these combinations of radio buttons:

• After and Insert
• After and Update of columns

For each

Select a radio button to specify whether the triggered action is to occur when the selected operation acts on each row, or when the operation is over:

Row

The action occurs every time the operation acts on a row. If you selected the Before radio button on the Trigger page, the action precedes the operation. If you selected theAfter radio button, the action follows the operation.

Statement

The action occurs one time after the operation acts on all rows. This radio button is available only if you selected the After radio button.

Triggered action

This field displays a template for the SQL for the action that is to precede or follow the operation selected on the Trigger page (inserting rows, deleting them, or updating them). In this field, specify the entire action. Use one or both of these methods:

• Type the SQL.

• Copy any or all of the SQL from the definition of an existing trigger.

The template includes a keyword and placeholder (WHEN (search-condition)) for an optional search condition, keywords (BEGIN ATOMIC and END) to delimit SQL statements, and a placeholder (triggered-SQL-statement;) for these statements.

Erase any part of the template that you don't need. If you need to use WHEN, you must replace (search-condition) with your search condition. If you need BEGIN ATOMIC and END, you must replace triggered--SQL--statement; with your SQL statements.

To remove the last change made to this field, click on Undo. To remove all content, click on Clear.

Undo

Click on this push button to remove the last change made to the Triggered action field.

Clear

Click on this push button to remove the content of the Triggered action field.