CREATE TRIGGER (Define a trigger)

Function

Upon operation on a specified table (INSERT, UPDATE, and DELETE statements), CREATE TRIGGER defines an action (trigger) that automatically executes SQL statements.

Privileges

A user can define a trigger on tables that he or she owns.

Format

CREATE TRIGGER [authorization-identifier.]trigger-identifier
  trigger-action-time
  trigger-event
  ON [authorization-identifier.]table-identifier
  [REFERENCING old-or-new-values-alias-list]
  trigger-action
  [SQL-compile-option[SQL-compile-option]...]
  [WITH PROGRAM]

trigger-action-time ::={BEFORE|AFTER}
trigger-event ::={INSERT|DELETE|UPDATE[OF column-name[, column-name]...]}
old-or-new-values-alias-list ::=old-or-new-values-alias[old-or-new-values-alias]
old-or-new-values-alias ::={OLD [ROW] [AS] old-values-correlation-name
    |NEW [ROW] [AS] new-values-correlation-name}
old-values-correlation-name, new-values-correlation-name ::=correlation-name
trigger-action ::=[{FOR EACH ROW|FOR EACH STATEMENT}]
    [WHEN (search-condition)]
    trigger-SQL-statement
  trigger-SQL-statement ::=SQL-procedure-statement
SQL-compile-option ::={ISOLATION data-guarantee-level [FOR UPDATE EXCLUSIVE]
    |OPTIMIZE LEVEL SQL-optimization-option
     [, SQL-optimization-option]...
     |ADD OPTIMIZE LEVEL SQL-extension-optimizing-option
     [, SQL-extension-optimizing-option]...
|SUBSTR LENGTH maximum-character-length }

Operands

authorization-identifier
Specifies the authorization identifier of the owner of the trigger being defined. The default is the authorization identifier of the user who executes CREATE TRIGGER.
trigger-identifier
Specifies the name of the trigger being defined.
BEFORE
Executes the trigger action before an operation is performed on the table.
If BEFORE is specified, function calls other than data update SQL statements (INSERT, UPDATE, or DELETE statements), the CALL statement, and the default constructor function cannot be specified in trigger-SQL-statement.
The trigger for which BEFORE is specified in trigger-action-time is called a BEFORE trigger.
AFTER
Executes the trigger action after an operation is performed on the table.
The trigger for which AFTER is specified in trigger-action-time is called an AFTER trigger.

Specifies the type of operation that induces the execution of the trigger.

INSERT
Executes a trigger when a row is inserted into the table. The trigger for which INSERT is specified in trigger-event is called an INSERT trigger.
If INSERT is specified in trigger-event, OLD[ROW][AS] old-values-correlation-name cannot be specified in old-or-new-values-alias.
DELETE
Executes a trigger when a row is deleted from the table. The trigger for which DELETE is specified in trigger-event is called a DELETE trigger.
If DELETE is specified in trigger-event, NEW[ROW][AS] new-values-correlation-name cannot be specified in old-or-new-values-alias.
UPDATE
Executes a trigger when a row is updated in the table. The trigger for which UPDATE is specified in trigger-event is called an UPDATE trigger.
If UPDATE is specified in trigger-event, a trigger is executed even if the value does not change before and after the update, provided that the trigger action conditions are satisfied.
OF column-name[, column-name]...
For executing a trigger upon updating of a specific column, specify OF column-name[, column-name].... The columns specified here are called trigger event columns.
The following rules apply to trigger event columns:
  1. In column-name, specify the column name of the table for which a trigger is defined.
  2. Column names cannot be specified in duplicate.
  3. If a repetition column is specified, a trigger is executed when an UPDATE statement containing only an ADD or DELETE clause is executed for the column.
  4. If a trigger event column is omitted, the default is all column names for the target table (including columns that are added after the trigger is defined). The trigger is executed when an UPDATE statement containing only an ADD or DELETE clause is executed.

Specifies the table name of the base table for which a trigger is being defined.

A user can define a trigger only for the tables that he or she owns. A trigger cannot be defined for a view table.

When referencing a row before and after an update as part of a trigger definition, specifies an alias.

In old-or-new-values-alias, either OLD[ROW][AS] old-values-correlation-name or NEW[ROW][AS]new-values-correlation-name can be specified only once.

This option is specified when referencing a row either before or after updating by assigning a name to it.

The following rules apply to old or new values aliases:

  1. ROW and AS have the same effect, irrespective of whether or not they are specified.
  2. The same correlation name cannot be specified in both old-values-correlation-name and new-values-correlation-name.
  3. The scope of a correlation name specified in either old-values-correlation-name or new-values-correlation-name is the entire trigger definition.
  4. If a column qualified with new-values-correlation-name is updated by using a BEFORE trigger, the update takes effect in the table. However, a BEFORE trigger, cannot update any of the following columns qualified with new-values-correlation-name; an attempt to update such a column may produce a runtime error:
    • Columns for which SYSTEM GENERATED is specified
    The BEFORE trigger for which the trigger event is INSERT cannot update any of the following columns by qualifying them with new-values-correlation-name; an attempt to update such a column may produce a runtime error:
    • Columns that are specified in the partitioning key for a partitioned table (exclusive of flexible hash-partitioned tables)
    For the BEFORE trigger, even when the updating is performed by qualifying a name with new-values-correlation-name, the insertion value (trigger event: INSERT) or the update value (trigger event: UPDATE) before updating must be a value that can be inserted into a specified column or a value that can be used for updating the column. For example, NULL cannot be specified as an insertion or update value before updating by means of a trigger action if the specified column is NOT NULL-constrained. With regard to uniqueness constraints, however, insertion and updating can be performed if the value that is updated by a trigger action satisfies the uniqueness constraint.
  5. Repetition columns and abstract data-type columns cannot be referenced by qualifying them with new-values-correlation-name and old-values-correlation-name (in a trigger definition, repetition columns and abstract data-type columns cannot be referenced in the table in which a trigger is defined).
  6. ROW cannot be specified in old-values-correlation-name or in new-values-correlation-name.
OLD [ROW] [AS] old-values-correlation-name
This operand is specified when referencing a row, before updating it, by assigning a name to it.
The value stored in the column qualified with old-values-correlation-name is a value that was in effect before the SQL statement that caused the trigger was executed. For the UPDATE statement, it is a pre-update value; for the DELETE statement, it is the column value of the row to be deleted.
NEW [ROW] [AS] new-values-correlation-name
This operand is specified when referencing a row, after updating it, by assigning a name to it.
The value stored in the column qualified with new-values-correlation-name is a value that is the result of execution of the SQL statement that caused the trigger. For the UPDATE statement, it is a post-update value; for the INSERT statement, it is the inserted value. If, however, a column qualified with new-values-correlation-name is updated during the trigger event, the updated value is inherited.
FOR EACH ROW
This option is specified when executing a trigger by updated row. The trigger for which FOR EACH ROW is specified in trigger-action is called a trigger by row. The FOR EACH ROW option causes the execution of the trigger each time the one row in the table is updated.
FOR EACH STATEMENT
This option is specified when executing a trigger by SQL statement. The trigger for which FOR EACH STATEMENT is specified in trigger-action is called a trigger by statement.
FOR EACH STATEMENT causes the execution of the trigger by each SQL statement, in which case the trigger is executed even where there are no rows to be updated.
FOR EACH STATEMENT cannot be specified together with old-or-new-values-alias-list.
search-condition
When a trigger event occurs, the trigger SQL statement is executed if the condition specified here is true. Such a search condition is called a trigger action condition.
If the operand trigger-action-condition is omitted, the trigger SQL statement is always executed when a specified trigger event occurs. Any of the following items cannot be specified in the trigger-action-condition operand:
  • Subqueries
  • Set function or SQL/XML set function
  • Window function
  • Repetition columns
  • Embedded variables, ? parameters, SQL variables, and SQL parameters
  • Columns of any of the following data types:
    [Figure]BLOB (however, the item can be specified in the scalar functions LENGTH and POSITION, and in function calls for a user-defined function).
    [Figure]BINARY with a maximum length of 32,001 bytes or greater (however, the item can be specified in the scalar functions LENGTH and POSITION, and in function calls for a user-defined function).
    [Figure]Abstract data type
  • Structured repetition predicates
  • Plug-in functions
  • Value expressions producing a result that is an abstract data type (not specifiable in a value expression)
    When referencing a column of the table for which a trigger is defined in a trigger action condition, qualify the column with old-or-new-values-correlation-name (non-qualified columns cannot be specified, and columns qualified with a table name cannot be specified).
  • XML constructor function
  • SQL/XML scalar function
trigger-SQL-statement
Specifies an SQL procedure statement. For details about SQL procedure statements, see 7. Routine Control SQL.
SQL procedure statements that are specified as a trigger SQL statement are subject to the following restrictions:
  1. The table name of a table for which a trigger is defined cannot be specified.
  2. Columns in the table for which a trigger is defined cannot be specified either without qualifying them or by qualifying them with a table name (columns qualified with an old- or new-values alias can be specified). A column qualified with an old- or new-values correlation name can be specified in the trigger SQL statement in the same locations as locations where an SQL parameter can be specified in an SQL statement. For details about locations where an SQL parameter can be specified, see 1.6 Embedded variables, indicator variables, ? parameters, SQL parameters, and SQL variables. Such a column, however, cannot be specified in the LIMIT clause.
  3. ROLLBACK, COMMIT, and PURGE TABLE statements cannot be specified. Calling a procedure specifying any of these statements using a CALL statement can produce a runtime error.
  4. JAVA stored procedures and the GET_JAVA_STORED_ROUTINE_SOURCE specification cannot be specified. Calling a procedure specifying a JAVA stored procedure using a CALL statement can produce a runtime error.

In SQL-compile-option, ISOLATION, OPTIMIZE LEVEL, ADD OPTIMIZE LEVEL, and SUBSTR LENGTH can each be specified only once.

[ISOLATION data-guarantee-level] [FOR UPDATE EXCLUSIVE]]
Specifies an SQL data integrity guarantee level.
data-guarantee-level
A data integrity guarantee level specifies the point to which the integrity of the transaction data must be guaranteed. The following data integrity guarantee levels can be specified:
  • 0
    This option is specified when the integrity of data is not to be guaranteed. Level 0 permits the referencing of data even when the data is being updated by another user, without waiting for completion of the update process. However, if the table to be referenced is a shared table and another user is executing the LOCK statement in the lock mode, the system waits until the lock condition is released.
  • 1
    This option is specified when the integrity of data is to be guaranteed until the end of the retrieval process. Level 1 prevents other users from updating retrieved data until the retrieval process is completed (until HiRDB finishes viewing the page or row).
  • 2
    This option is specified when the integrity of retrieved data is to be guaranteed until the end of a transaction. Level 2 prevents other users from updating retrieved data until termination of the transaction.
[FOR UPDATE EXCLUSIVE]
Specify this operand if WITH EXCLUSIVE LOCK is always to be assumed, irrespective of the data guarantee level specified in SQL-compile-option for a cursor or query in a procedure for which the FOR UPDATE clause is specified or assumed. If level 2 is specified in data-guarantee-level, WITH EXCLUSIVE LOCK is assumed for the cursor or query in a procedure for which the FOR UPDATE clause is specified or assumed, in which case it is not necessary to specify FOR UPDATE EXCLUSIVE.
Relationship to the client environment definition
PDISLLVL and PDFORUPDATEEXLOCK, if specified on CREATE TRIGGER, have no effect.
Relationship to SQL statements
If a lock option is specified in an SQL statement in a procedure, the lock option specified in the SQL statement takes precedence over any data guarantee level specified in SQL-compile-option or the lock option assumed due to FOR UPDATE EXCLUSIVE.
The default for this operand is Level 2.
For data guarantee levels, see the HiRDB Version 9 UAP Development Guide.
[OPTIMIZE LEVEL SQL-optimization-option[, SQL-optimization-option]...]
Specifies an optimization method for determining the most efficient access path by taking the condition of the database into consideration.
An SQL optimization option can be specified using either an identifier (character string) or a numeric value. For most cases, Hitachi recommends the use of an identifier.
Specifying with an identifier

OPTIMIZE LEVEL "identifier"[, "identifier"]...

Specification examples
  • Applying prioritized nest-loop-join and rapid grouping processing:
    OPTIMIZE LEVEL "PRIOR_NEST_JOIN", "RAPID_GROUPING"
  • Applying no optimization:
    OPTIMIZE LEVEL "NONE"
Rules
  1. Specify one or more identifiers.
  2. When specifying two or more identifiers, delimit them with commas (,).
  3. For details about the contents that can be specified in an identifier (optimization methods), see Table 3-41 SQL optimization option specification values (CREATE TRIGGER).
  4. If no optimization is to be applied, specify NONE in identifier. If an identifier other than NONE is specified at the same time, NONE is nullified.
  5. Identifiers can be specified in both lower case and upper case characters.
  6. If the same identifier is specified more than once, it is treated as if it was specified only once; however, when possible, precautions should be taken to avoid specifying a given identifier in duplicate.
Specifying with a numeric value

OPTIMIZE LEVEL unsigned-integer[, unsigned-integer]...

Specification examples
  • Making multiple SQL objects, suppressing the use of AND multiple indexes, and forcing the use of multiple indexes
    For specifying unsigned integers by delimiting them with commas:
    OPTIMIZE LEVEL 4, 10, 16
    For specifying the sum of unsigned integers:
    OPTIMIZE LEVEL 30
  • Adding a new value, 16, with the value 14 (4 + 10) already specified:
    OPTIMIZE LEVEL 14, 16
  • Applying no optimization:
    OPTIMIZE LEVEL 0
Rules
  1. When HiRDB is upgraded from a version earlier than Version 06-00 to a Version 06-00 or later, the total value specification in the earlier version also remains valid. If the optimization option does not need to be modified, the specification value for this operand need not be changed when HiRDB is upgraded to a Version 06-00 or later.
  2. Specify one or more unsigned integers.
  3. When specifying two or more unsigned integers, separate them with commas (,).
  4. For details about the contents that can be specified in an unsigned integer (optimization methods), see Table 3-41 SQL optimization option specification values (CREATE TRIGGER).
  5. When not applying any optimization, specify 0 in unsigned-integer. However, specifying an identifier other than 0 at the same time nullifies the 0.
  6. If the same unsigned integer is specified more than once, it is treated as if it was specified only once; however, when possible, precautions should be taken to avoid specifying a given unsigned integer in duplicate.
  7. When specifying multiple optimization methods, you can specify the sum of their unsigned integers. However, care should be taken not to add the value of the same optimization method multiple times (to prevent the possibility of the resulting sum from being interpreted as a separate optimization method).
  8. To specify multiple optimization methods by adding their values, Hitachi recommends to separate each optimization method specification with a comma to avoid ambiguities regarding which optimization method is being specified. If a new optimization method needs to be specified after multiple optimization methods have been specified by adding their values, specify the new value by appending it, separated with a comma.
Relationship to system definitions
  1. The default for the SQL optimization option is the value specified in the pd_optimize_level operand of the system definitions. For details about the pd_optimize_level operand, see the manual HiRDB Version 9 System Definition.
  2. When the pd_floatable_bes operand or the pd_non_floatable_bes operand is specified, specification of the Increasing the target floatable servers (back-end servers for fetching data) option or the Limiting the target floatable servers (back-end servers for fetching data) option, respectively, is invalid.
  3. When KEY is specified in the pd_indexlock_mode operand of the system definition (i.e., for index key value lock), specification of the Suppressing creation of update-SQL work tables option is invalid.
Relationship to the client environment definition
Specification of PDSQLOPTLVL has no effect on CREATE TRIGGER.
Relationship with SQL
If SQL optimization is specified in an SQL statement, the SQL optimization specification takes precedence over SQL optimization options. For SQL optimization specifications, see 2.24 SQL optimization specification.
SQL optimization option specification values
The following table lists the SQL optimization option specification values. For details about optimization methods, see the HiRDB Version 9 UAP Development Guide.

Table 3-41 SQL optimization option specification values (CREATE TRIGGER)

No.Optimization methodSpecification value
IdentifierUnsigned integer
1Forced nest-loop-join"FORCE_NEST_JOIN"4
2Making multiple SQL objects"SELECT_APSL"10
3Increasing the target floatable servers (back-end servers for fetching data)#1, #2"FLTS_INC_DATA_BES"16
4Prioritized nest-loop-join"PRIOR_NEST_JOIN"32
5Increasing the number of floatable server candidates#2"FLTS_MAX_NUMBER"64
6Priority of OR multiple indexes"PRIOR_OR_INDEXES"128
7Group processing, ORDER BY processing, and DISTINCT set function processing at the local back-end server#2"SORT_DATA_BES"256
8Suppressing the use of AND multiple indexes"DETER_AND_INDEXES"512
9Rapid grouping processing"RAPID_GROUPING"1024
10Limiting the target floatable servers (back-end servers for fetching data)#1, #2"FLTS_ONLY_DATA_BES"2048
11Separating data collecting servers#1, #2"FLTS_SEPARATE_COLLECT_SVR"2064
12Suppressing index use (forced table scan)"FORCE_TABLE_SCAN"4096
13Forcing use of multiple indexes"FORCE_PLURAL_INDEXES"32768
14Suppressing creation of update-SQL work tables"DETER_WORK_TABLE_FOR_UPDATE"131072
15Derivation of rapid search conditions"DERIVATIVE_COND"262144
16Applying key conditions including scalar operations"APPLY_ENHANCED_KEY_COND"524288
17Facility for batch acquisition from functions provided by plug-ins"PICKUP_MULTIPLE_ROWS_PLUGIN"1048576​
18Facility for moving search conditions into derived table"MOVE_UP_DERIVED_COND"2097152​
#1: If both Increasing the target floatable servers (back-end servers for fetching data) and Limiting the target floatable servers (back-end servers for fetching data) are specified together, the respective optimization method does not take effect; instead, the specification operates as a separating data collecting server.
#2: When specified on a HiRDB/Single Server, this option has no effect.
[ADD OPTIMIZE LEVEL SQL-extension-optimizing-option[, SQL-extension-optimizing-option]...]
Specifies an optimization method for determining the most efficient access path by taking the condition of the database into consideration.
An SQL extension optimizing option can be specified using either an identifier (character string) or a numeric value.
Specifying with an identifier

ADD OPTIMIZE LEVEL "identifier"[, "identifier"]...

Specification examples
  • Applying Optimizing mode 2 based on cost and Hash join, subquery hash execution:
    ADD OPTIMIZE LEVEL "COST_BASE_2", "APPLY_HASH_JOIN"
  • Applying no optimization:
    ADD OPTIMIZE LEVEL "NONE"
Rules
  1. Specify one or more identifiers.
  2. When specifying two or more identifiers, delimit them with commas (,).
  3. For details about the contents that can be specified in an identifier (optimization methods), see Table 3-42 SQL extension optimizing option specification values (CREATE TRIGGER).
  4. If no optimization is to be applied, specify NONE in identifier.
  5. Identifiers can be specified in both lower case and upper case characters.
  6. If the same identifier is specified more than once, it is treated as if it was specified only once; however, when possible, precautions should be taken to avoid specifying a given identifier in duplicate.
Specifying with a numeric value

ADD OPTIMIZE LEVEL unsigned-integer[, unsigned-integer]...

Specification examples
  • Applying Optimizing mode 2 based on cost and Hash join, subquery hash execution:
    ADD OPTIMIZE LEVEL 1, 2
  • Applying no optimization:
    ADD OPTIMIZE LEVEL 0
Rules
  1. Specify one or more unsigned integers.
  2. When specifying two or more unsigned integers, separate them with commas (,).
  3. For details about the contents that can be specified in an unsigned integer (optimization methods), see Table 3-42 SQL extension optimizing option specification values (CREATE TRIGGER).
  4. When not applying any optimization, specify 0 in unsigned-integer.
  5. If the same unsigned integer is specified more than once, it is treated as if it was specified only once; however, when possible, precautions should be taken to avoid specifying a given unsigned integer in duplicate.
Relationship to system definitions
The default for the SQL extension optimizing option is the value specified in the pd_additional_optimize_level operand of system definitions. For details about the pd_additional_optimize_level operand, see the manual HiRDB Version 9 System Definition.
Relationship to the client environment definition
Specification of PDADDITIONALOPTLVL has no effect on CREATE TRIGGER.
Relationship with SQL
If SQL optimization is specified in an SQL statement, the SQL optimization specification takes precedence over SQL optimization options. For SQL optimization specifications, see 2.24 SQL optimization specification.
SQL extension optimizing option specification values
The following table lists the SQL optimization option specification values. For details about optimization methods, see the HiRDB Version 9 UAP Development Guide.

Table 3-42 SQL extension optimizing option specification values (CREATE TRIGGER)

No.Optimization methodSpecification value
IdentifierUnsigned integer
1Application of optimizing mode 2 based on cost"COST_BASE_2"1
2Hash join, subquery hash execution"APPLY_HASH_JOIN"2
3Facility for applying join conditions including value expression"APPLY_JOIN_COND_FOR_VALUE_EXP"32
Note
Items 2 and 3 take effect when Application of optimizing mode 2 based on cost is specified.
[SUBSTR LENGTH maximum-character-length]
Specifies the maximum number of bytes for representing a single character.
The value specified for the maximum character length must be in the range from 3 to6.
This operand is valid only when utf-8 is specified for the character code type in the pdntenv command (pdsetup command for the UNIX edition); it affects the length of the result of the SUBSTR scalar function. For details about SUBSTR, see 2.16.1(20) SUBSTR.
Relationships to system definition
When SUBSTR LENGTH is omitted, the value specified in the pd_substr_length operand in the system definition is assumed. For details about the pd_substr_length operand, see the manual HiRDB Version 9 System Definition.
Relationship to client environmental definition
The specification of PDSUBSTREN has no applicability to CREATE TRIGGER. For details about PDSUBSTRLEN, see the manual HiRDB Version 9 UAP Development Guide.
Relationship to the character code type specified in the pdntenv or pdsetup command
This operand is valid only when utf-8 is specified for the character code type.
For all other character code types, only a syntax check is performed and the specification is ignored.

When defining a trigger, if there is a function, a procedure, or an SQL object for which a trigger is in effect and that uses the table for which the trigger is being defined, specify this option when nullifying the SQL object:

All functions and procedures that created an SQL object in HiRDB of Version 07-00 or earlier are nullified even if they do not meet the above conditions.

Common rules

  1. Defining a trigger causes the creation of an SQL object called a trigger action procedure, which is stored in the data dictionary LOB RDAREA. For this reason, before defining a trigger, you need to allocate sufficient space in the LOB RDAREA. For details about how to estimate the required space for a data dictionary LOB RDAREA, see the HiRDB Version 9 Installation and Design Guide.
  2. The routine identifier for the trigger action procedure takes the following name with a length of 22 bytes:
    '(TRIGyyyymmddhhmmssth)'
    yyyymmddhhmmssth: trigger-definition-time-stamp (in 10 milliseconds)
  3. The specific name for the trigger action procedure is the same as [authorization-identifier.]trigger-action-procedure-routine-identifier. The trigger action procedure routine identifier is stored in the SPECIFIC_NAME column of the SQL_TRIGGERS dictionary table.
  4. The user defining a trigger must have the privilege necessary for the execution of the trigger SQL statement. Whether the user defining a trigger has the requisite privilege is checked when the trigger is defined and executed.
  5. If WITH PROGRAM is omitted, a trigger cannot be defined if there is a function or a procedure that uses the table for which the trigger is being defined and needs to modify the SQL object, and there is an SQL object for which a trigger is in effect.
  6. When specifying an SQL compile option in ALTER TRIGGER or ALTER ROUTINE, make sure that any SQL statements created by incorporating the SQL compile option into the source CREATE TRIGGER statement of the trigger that is regenerating the SQL object do not exceed the maximum allowable length for SQL statements.
  7. If the SQL object being executed becomes nullified, CREATE TRIGGER cannot be executed from within a Java procedure.

Rules on executing a trigger-inducing SQL statement

  1. A trigger occurs only on a specified trigger event (UPDATE or DELETE statement); it does not occur on a PURGE TABLE statement, the database load utility, the database reorganization facility, or on re-initialization of the RDAREA.
  2. The sequence in which a trigger is executed is shown as follows:

    [Figure]

  3. If multiple triggers having the same trigger action time, trigger event, and trigger action unit (by statement or by row) are defined, the trigger actions are executed in the order they are defined (in the order of the values of the CREATE_TIME columns in the SQL_TRIGGERS table).
  4. If an error occurs during execution of a trigger, the affected transaction is nullified (a rollback is triggered implicitly). If, during the execution of a trigger-inducing SQL statement, an error occurs after a trigger is executed once, the affected transaction is nullified irrespective of whether the trigger is being executed or whether the transaction is to be nullified. However, a rollback does not occur for a table for which WITHOUT ROLLBACK is specified, after completion of row updating (including additions and deletions). For details, see the rules applicable to WITHOUT ROLLBACK in CREATE TABLE (Define table) in this chapter.
  5. The user executing the trigger-inducing SQL statement need not have the privilege necessary for execution of the trigger SQL statement.
  6. Other triggers can also be executed upon encountering an SQL statement in the trigger SQL statement (in nesting of triggers). Only 16 levels of triggers can be nested. If a 16th level trigger causes an operation that sets off other triggers, an error may occur.
  7. A row-by-row trigger for which UPDATE is specified in trigger-event is executed only once relative to the row to be updated. Even when triggers are started by nesting, and the same trigger is started as an extension of the nesting, the row-by-row trigger is executed only once, the first time.
  8. Any of the following items cannot be specified on a table for which the UPDATE trigger is defined:
    • An update using a component specification on a table for which an UPDATE trigger specifying a new-values correlation name is defined.
    • An update of the BLOB type using a concatenation operation or the BINARY type with a defined length of 32,001 bytes or greater, for a table for which the UPDATE trigger is defined
  9. When referencing a table that is updated by a trigger operation or its extension in an SQL subquery that causes a row-unit AFTER trigger, make sure that the subquery does not include an external reference.

Notes

  1. CREATE TRIGGER cannot be specified from an X/Open compliant UAP running under OLTP.
  2. When you specify an update SQL statement (UPDATE, DELETE, or INSERT statement) in a trigger SQL statement, the following SQL errors can occur during the execution of the specified update SQL statement:
    • The table specified in the update SQL statement is included in the SQL statement that executes the FOR EACH ROW trigger specifying the update SQL statement.
    • The table specified in the update SQL statement is included in the FOR EACH ROW trigger definition (including the nesting of multiple trigger definitions) that executes the FOR EACH ROW trigger definition specifying the update SQL statement.
    In this case, either specify index key-value-non-locking in system definitions or change the trigger definition.
  3. Specifying an update SQL statement in a trigger SQL statement can affect the retrieval of the SQL during the execution of the specified update SQL statement. To avoid any impact on the retrieval of the SQL statement being executed, search conditions and data need to be modified so that the update SQL statement does not match the retrieval conditions for the SQL statement being executed. Special care should be exercised if trigger definitions are nested.
  4. If a function, a procedure, or an SQL object for which a trigger is in effect is nullified through the specification of WITH PROGRAM, any rows related to the nullified function, procedure, or trigger are deleted from the SQL_ROUTINE_RESOURCES dictionary table.
  5. Before executing the function, procedure, or trigger SQL object that was nullified through the specification of WITH PROGRAM, you need to re-create the nullified function, procedure, and trigger SQL object by executing ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER.
  6. The data guarantee level of the trigger SQL statement in the trigger, the SQL optimization option, the SQL extension optimizing option, and the maximum character length are determined by what is specified when the trigger is being defined or modified, and are not affected by the system definition or client environment variable definition that is in effect when the trigger action is executed.
  7. The trigger action is also executed if the operation specified in trigger-event is executed on the view table for which the table specified in ON [authorization-identifier.]table-identifier is the base table.
  8. If the trigger SQL object is not in effect and any of the following SQL statements is executed by using the table defining that trigger, an error occurs irrespective of whether the trigger action condition is true or false. Similarly, if the index for the trigger SQL object is not in effect and any of the following SQL statements is executed by using the table that defined the trigger, an error occurs if the trigger action condition is true:
    • If trigger-event is INSERT, an INSERT statement that inserts rows into the table defining the trigger
    • If trigger-event is UPDATE, an UPDATE statement that updates rows in the table defining the trigger
    • If trigger-event is DELETE, a DELETE statement that deletes rows from the table defining the trigger
  9. Nested triggers can degrade performance; to the maximum possible extent, nesting of triggers should be avoided.
  10. When creating nested triggers, creating triggers from the leading trigger can result in an error during the creation of nested triggers. A WITH PROGRAM specification can create nested triggers, but in this case the first trigger is in a nullified state. Therefore, when creating nested triggers, create them in sequence beginning with the trigger that is at the end of the nesting levels.

Examples

The CREATE TRIGGER example uses an inventory history table (HSTOCK), a Glasgow inventory table, and a Edinburgh inventory table in addition to an inventory table (STOCK). The Glasgow and Edinburgh inventory tables have the same organization as the inventory table. The inventory history table is organized as follows:

[Figure]

  1. After a row is inserted into the inventory table (STOCK), define a trigger (INSERTTRIG1) that inserts information on the inserted row into the inventory history table (HSTOCK):

    CREATE TRIGGER INSERTTRIG1
     AFTER INSERT ON STOCK
     REFERENCING NEW ROW X1
     FOR EACH ROW
     INSERT INTO HSTOCK
       VALUES(X1.PCODE,NULL,X1.SQTY,
              CURRENT_DATE,CURRENT_TIME)

  2. After the quantity (SQTY) is updated in the inventory table (STOCK), define a trigger (INSERTTRIG2) that inserts pre- and post-update values into the inventory history table (HSTOCK):

    CREATE TRIGGER INSERTTRIG2
     AFTER UPDATE OF SQTY ON STOCK
     REFERENCING NEW ROW X1 OLD ROW Y1
     FOR EACH ROW
     INSERT INTO HSTOCK
       VALUES(Y1.PCODE,Y1.SQTY,X1.SQTY,
              CURRENT_DATE,CURRENT_TIME)

  3. After a row is deleted from the inventory table (STOCK), define a trigger (INSERTTRIG3) that inserts information on the deleted row into the inventory history table (HSTOCK):

    CREATE TRIGGER INSERTTRIG3
     AFTER DELETE ON STOCK
     REFERENCING OLD ROW Y1
     FOR EACH ROW
     INSERT INTO HSTOCK
       VALUES(Y1.PCODE,Y1.SQTY,NULL,
       CURRENT_DATE,CURRENT_TIME)

  4. Use a routine control SQL (a compound statement) in trigger-action. After the inventory table (STOCK) is updated, define a trigger (UPDATELOCAL) that puts the update into effect in the Glasgow and Edinburgh inventory tables. If a compound statement is not used, two triggers that are set off upon the updating of the inventory table must be defined.

    CREATE TRIGGER UPDATELOCAL
     AFTER UPDATE OF SQTY ON STOCK
     REFERENCING NEW ROW X1 OLD ROW Y1
     BEGIN
       UPDATE glasgow-inventory-table SET SQTY=X1.SQTY
         WHERE PCODE=Y1.PCODE;
       UPDATE edinburgh-inventory-table SET SQTY=X1.SQTY
         WHERE PCODE=Y1.PCODE;
     END

  5. Use a routine control SQL (an assignment statement) in trigger-action. Define a trigger (SETPRICE) that assigns the following amount to the row to be inserted into the inventory table (STOCK): an amount equal to the unit price (PRICE) to be inserted plus 50.00 dollars if the product code (PCODE) is 101M, 201M, or an amount equal to the unit price to be inserted multiplied by 1.2 if the (PCODE) is not 101M, 201M, or 301M:

    CREATE TRIGGER SETPRICE
     BEFORE INSERT ON STOCK
     REFERENCING NEW ROW AS X1
     FOR EACH ROW
     SET X1.PRICE=CASE X1.PCODE
         WHEN '101M' THEN X1.PRICE + 50
         WHEN '201M' THEN X1.PRICE + 50
         WHEN '301M' THEN X1.PRICE + 50
         ELSE X1.PRICE * 1.2
       END

  6. Use an SQL diagnostic statement (SIGNAL statement) in trigger-action. Define a trigger (SIGNALTRIG) that suppresses the deletion of rows from the inventory table before rows in the inventory table (STOCK) are deleted:

    CREATE TRIGGER SIGNALTRIG
     BEFORE DELETE ON STOCK
     SIGNAL SQLSTATE '99001'