ALTER TRIGGER (Re-create a trigger SQL object)

Function

ALTER TRIGGER re-creates a trigger SQL object.

Privileges

Owner of a trigger
This user can re-create SQL objects for his or her own trigger.
DBA privilege users
These users can re-create their own triggers and SQL objects that are owned by other users.

Format

 ALTER TRIGGER [authorization-identifier.]trigger-identifier
     {CHANGE|ALTER} ROUTINE OBJECT
     [SQL compile-option[SQL compile-option]...]

 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 for which an SQL object is to be re-created. The default is the authorization identifier of the user who executes ALTER TRIGGER.
trigger-identifier
Specifies the identifier for the trigger for which an SQL object is to be re-created.

This indicates that a trigger is to be re-created. Either CHANGE or ALTER can be specified without a change in meaning.

In SQL compile-option, ISOLATION, OPTIMIZE LEVEL, ADD OPTIMIZE, 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. If a data guarantee level is specified in SQL compile-option and FOR UPDATE EXCLUSIVE is omitted, it is assumed that FOR UPDATE EXCLUSIVE is not specified.
Relationship to client environment definition
Any specification of PDISLLVL or PDFORUPDATEEXLOCK with respect to ALTER TRIGGER has no effect.
Relationship to SQL statements
If the lock option is specified in an SQL statement in a procedure, the lock option specified in the SQL statement takes precedence over the data guarantee level specified in SQL compile-option or the lock option assumed from FOR UPDATE EXCLUSIVE.
The default for this operand is the value that was specified during the previous SQL object creation (during the execution of CREATE TRIGGER, ALTER TRIGGER, or ALTER ROUTINE).
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.
The default for this operand is the value that was specified during the previous SQL object creation (CREATE TRIGGER, ALTER TRIGGER, or ALTER ROUTINE).
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 what can be specified in identifier (optimization methods), see Table 3-8 SQL optimization option specification values (ALTER 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, where possible, precautions should be taken to avoid specifying a given identifier in duplicate.
Specifying a numeric value

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

Specification examples
  • Creating 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 16 when 14 (4 + 10) is 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 what can be specified in an unsigned integer (optimization method), see Table 3-8 SQL optimization option specification values (ALTER TRIGGER).
  5. When not applying any optimization, specify 0 in unsigned-integer. If non-zero identifiers are specified at the same time, the specification of 0 is nullified.
  6. If the same unsigned integer is specified more than once, it is treated as if it was specified only once; however, where 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, you can specify the new value by appending it, separated with a comma.
Relationship to system definitions
  1. The system-defined pd_optimize_level operand, if specified for ALTER TRIGGER, has no effect.
  2. If the system-defined pd_floatable_bes or pd_non_floatable_bes operand is specified, any specification of Increasing the target floatable servers (back-end servers for fetching data) or Limiting the target floatable servers (back-end servers for fetching data) has no effect.
  3. If KEY is specified for the system-defined pd_indexlock_mode operand, (for index key value-locking), any specification of Suppressing creation of update-SQL work tables has no effect.
Relationship to client definitions
PDSQLOPTLVL, if specified, has no effect on ALTER TRIGGER.
Relationship to 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-8 SQL optimization option specification values (ALTER 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 index use"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 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.
The default for this operand is the value that was specified during the previous SQL object creation (CREATE TRIGGER, ALTER TRIGGER, or ALTER ROUTINE).
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 what can be specified in identifier (optimization methods), see Table 3-9 SQL extension optimizing option specification values (ALTER 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, where possible, precautions should be taken to avoid specifying a given identifier in duplicate.
Specifying 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 what can be specified in an unsigned integer (optimization method), see Table 3-9 SQL extension optimizing option specification values (ALTER 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, where possible, precautions should be taken to avoid specifying a given unsigned integer in duplicate.
Relationship to system definitions
The system-defined pd_optimize_level operand, if specified for ALTER TRIGGER, has no effect.
Relationship to client environment definition
PDADDITIONALOPTLVL, when specified, has no effect for ALTER 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 extension optimizing option specification values. For details about optimization methods, see the HiRDB Version 9 UAP Development Guide.

Table 3-9 SQL extension optimizing option specification values (ALTER 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 to 6.
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.
Rules
When HiRDB is upgraded from a version earlier than version 08-00 to version 08-00 or later, 3 is assumed. If there is no need to change the maximum character length, you do not need to specify this operand when upgrading to HiRDB of version 08-00 or later.
Relationships to system definition
When SUBSTR LENGTH is specified in ALTER TRIGGER, the pd_substr_length system definition operand has no effect. 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 ALTER 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 this operand is omitted, the value specified during creation of the most recent SQL object (execution of a CREATE TRIGGER, ALTER TRIGGER, or ALTER ROUTINE statement) is assumed.

Common rules

  1. Upon normal termination of the execution of ALTER TRIGGER for a trigger for which an SQL object is not in effect, the SQL object for that trigger takes effect.
  2. Upon normal termination of the execution of ALTER TRIGGER for a trigger for which the SQL object is in an index-disabled state, the trigger's SQL object is released from the index-disabled state. Triggers for which the SQL object is in an index-disabled state may result in a runtime error.
  3. When specifying an SQL compile option in ALTER TRIGGER, make sure that the SQL statement that is generated as a result of the SQL compile option with respect to CREATE TRIGGER for the source trigger to be re-created does not exceed the maximum allowable length for an SQL statement.
  4. Under the following condition, ALTER TRIGGER cannot be executed from a Java procedure:
    • The SQL object being executed is re-created.

Notes

  1. ALTER TRIGGER cannot be specified from an X-Open compliant UAP running under OLTP.
  2. Executing a GET DIAGNOSTICS statement immediately after the execution of ALTER TRIGGER enables you to acquire diagnostic information on ALTER TRIGGER. In this case, the trigger for which the re-creation process terminated normally produces an SQL code of 0.
  3. 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.

Example

  1. Re-create an SQL object for a trigger (TRIG1) that has been nullified:

    ALTER TRIGGER TRIG1
       CHANGE ROUTINE OBJECT