Creating a trigger

Use the Create Trigger window to create a trigger.

Authorities and privileges

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

Specifying the trigger's properties

To specify 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:

    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

To specify 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. You can:

    1. Click the Undo push button to revert the text to a previous state. There are four possible results:

      • Resets the text box to its initial state if you have modified the template.
      • Resets the text box to the text that existed just before clicking Clear, if Clear was clicked.
      • Resets the text box to the text that existed just before launching SQLAssist, if the SQLAssist push button was clicked.
      • Resets the text box to the text that existed just after launching SQLAssist, if text is modified after generating an SQL statement

    2. Click the Clear push button to erase the entire contents of the SQL statement text box.

    3. Click the SQLAssist push button to help you build an SQL statement that will be used as the triggered action. You can invoke SQLAssist multiple times to append additional SQL statements to the Triggered action field.