ALTER TRIGGER statement (advanced trigger)

Invocation for ALTER TRIGGER (advanced)

This statement can be issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES RUN behavior is in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization for ALTER TRIGGER (advanced)

The privilege set that is defined below must include at least one of the following:

• Ownership of the trigger
• The ALTERIN privilege on the schema
• System DBADM authority
• SYSCTRL authority
• SYSADM authority
• Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

The authorization ID that matches the schema name implicitly has the ALTERIN privilege on the schema.

Additional privileges might be required in the following situations:

• If trigger-specification is specified:
  • The privilege set must include at least one of the following:
    • Ownership of the trigger
    • DATAACCESS authority
    • SYSADM authority
  • The owner of the trigger must have SYSADM authority, DATAACCESS authority, or each one of the following:
    • The SELECT privilege on the table or view on which the trigger is defined.
    • The SELECT privilege on any table or view in the search-condition of the triggered-action.
    • The privileges that are required to execute the statements in SQL-trigger-body
• If the SECURED option is specified, the privilege set must include SECADM authority or the CREATE_SECURE_OBJECT privilege.
• If the WLM ENVIRONMENT FOR DEBUG MODE clause is specified, RACF® or an external security product is invoked to check the required authority for defining programs in the WLM environment. If the WLM environment access is protected in RACF, the user that issued the ALTER statement must have the required authority.

Additional authorization may be required on the SYSDUMMYx tables depending on the content of the trigger definition. See SYSDUMMYx tables.

Privilege set: The privilege set is the set of privileges that are held by the SQL authorization ID of the process. The specified trigger name can include a schema name (a qualifier). However, if the schema name is not the same as one of the authorization ID of the process, one of the following conditions must be met:

• The privilege set includes SYSADM authority, SYSCTRL authority, or system DBADM authority.
• The authorization ID of the process has the ALTERIN privilege on the schema.

Description for ALTER TRIGGER (advanced)

trigger-name

Identifies the trigger that is to be changed. The name, including the implicit or explicit schema name, must exist at the current server. The name must identify an advanced trigger.

The trigger must not be obfuscated.

ACTIVE VERSION or VERSION trigger-version-id

Identifies the version of the trigger that is to be changed, replaced, or regenerated depending on whether the ALTER, REPLACE, or REGENERATE keyword is specified.

ACTIVE VERSION

Specifies that the currently active version of the trigger is to be changed, replaced, or regenerated.

VERSION trigger-version-id

Identifies the version of the trigger that is to be changed, replaced, or regenerated. trigger-version-id is the version identifier that is assigned when the version is defined. trigger-version-id must identify a version of the specified trigger that exists at the current server.

USING APPLICATION COMPATIBILITY applcompat-level

Specifies the application compatibility level used to regenerate the version of the trigger. The ALTER statement returns an error if the existing definition of the version includes syntax, semantics, or options that require a higher application compatibility level. This situation can occur when the version was most recently defined or regenerated while running at a higher application compatibility level than applcompat-level.

The following values can be specified:

VvvRrMmmm

Compatibility with the behavior of the identified Db2 function level. For example, V12R1M510 specifies compatibility with the highest available Db2 12 function level. The equivalent function level or higher must be activated.

For the new capabilities that become available in each application compatibility level, see:

• SQL changes in Db2 13 application compatibility levels
• SQL changes in Db2 12 application compatibility levels

Tip: Extra program preparation steps might be required to increase the application compatibility level for applications that use data server clients or drivers to access Db2 for z/OS. For more information, see Setting application compatibility levels for data server clients and drivers.

V12R1

Compatibility with the behavior of Db2 12 function level 500. This value has the same result as specifying V12R1M500.

ALTER

Specifies that the trigger is to be changed. When you change one or more trigger options, any option that is not explicitly specified uses the existing value from the trigger that is being changed.

REPLACE

Specifies that a version of the trigger is to be replaced.

When you replace a trigger, the following trigger attributes must be the same as the corresponding attributes for the currently active version of the trigger: trigger-activation-time, trigger-event, table or view name, and trigger-granularity.

The content of the REFERENCING clause can differ from the other versions of the trigger. For options that are not explicitly specified, the system default values for those options are used, even if those options were explicitly specified for the version of the trigger that is being replaced. This is not the case for versions of the trigger that specified DISABLE DEBUG MODE. If DISABLE DEBUG MODE is specified for a version of a trigger, the option cannot be changed using the REPLACE clause. When a trigger definition is replaced, any existing comments in the catalog for that definition of the trigger are removed.

Binding the replaced version of the trigger might result in a new access path even if the trigger body is not changed.

ADD VERSION trigger-version-id

Specifies that a new version of the trigger is to be created. trigger-version-id is the version identifier for the new version of the trigger. trigger-version-id must not identify a version of the specified trigger that already exists at the current server.

When you add a new version of a trigger, the following trigger attributes must be the same as the corresponding attributes for the currently active version of the trigger: trigger-activation-time, trigger-event, table or view name, and trigger-granularity. The content of the REFERENCING clause can differ from the other versions of the trigger. For options that are not explicitly specified, the system default values are used.

ACTIVATE VERSION trigger-version-id

Specifies the version of the trigger that is to be the currently active version of the trigger. trigger-version- id is the version identifier that is assigned when the version of the trigger is defined. The version that is specified with trigger-version-id is the version that will be invoked when the trigger is activated. trigger-version-id must identify a version of the trigger that already exists at the current server.

REGENERATE

Specifies that the trigger package is to be regenerated.

The REGENERATE keyword on the ALTER TRIGGER statement is used to rebind the SQL control statements, as well as non-SQL-control statements that are included in the trigger body, at the local server. When a trigger is regenerated, any unqualified names in the trigger body are resolved using the name resolution process.

Using an ALTER TRIGGER statement with the REGENERATE keyword is different from a REBIND PACKAGE command. The REBIND PACKAGE command rebinds only the non-SQL-control statements to generate better access paths for those statements, using the statement text from the SYSIBM.SYSPACKSTMTS catalog table.

When a trigger definition is regenerated, any existing comments in the catalog for that definition of the trigger are not removed.

Generally, the REGENERATE keyword is used only for specific situations, such as when implicit regeneration fails for routines or objects, or Db2 maintenance requires objects or routines to be regenerated. For more information, see When to regenerate Db2 database objects and routines.

DROP VERSION trigger-version-id

Drops the version of the trigger that is identified with trigger-version-id. trigger-version-id is the version identifier that is assigned when the version is defined. trigger-version-id must identify a version of the trigger that already exists at the current server and must not identify the currently active version of the trigger. Only the identified version of the trigger is dropped.

When only a single version of the trigger exists at the current server, use the DROP TRIGGER statement to drop the trigger.

trigger-event

Specifies that the triggered action that is associated with the trigger is to be executed when the trigger event is applied to the subject table or view.

INSERT

Specifies that the trigger is an insert trigger. Db2 executes the triggered action whenever there is an insert operation on the subject table. However, if the insert trigger is defined on any explain table, and the insert operation was caused by Db2 adding a row to the table, the triggered action is not executed.

DELETE

Specifies that the trigger is a delete trigger. Db2 executes the triggered action whenever there is a delete operation on the subject table.

UPDATE

Specifies that the trigger is an update trigger. Db2 executes the triggered action whenever there is an update operation on the subject table.

If you do not specify a list of column names, an update operation on any column of the subject table, including columns that are subsequently added with the ALTER TABLE statement, activates the triggered action.

OF column-name,...

Each column-name that you specify must be a column of the subject table and must appear in the list only once. An update operation on any of the listed columns activates the triggered action.

UPDATE OF column-name cannot be specified for an INSTEAD OF trigger.

trigger-activation-time

NO CASCADE

NO CASCADE is allowed for compatibility with prior releases and other products.

BEFORE

Specifies that the trigger is a before trigger. Db2 executes the triggered action before it applies any changes caused by an insert, delete, or update operation on the subject table. It also specifies that the triggered action does not activate other triggers because the triggered action of a before trigger cannot contain any updates, REFRESH TABLE, or TRUNCATE SQL statements.

BEFORE must not be specified when view-name is also specified. FOR EACH ROW must be specified for a BEFORE trigger.

AFTER

Specifies that the trigger is an after trigger. Db2 executes the triggered action after it applies any changes caused by an insert, delete, or update operation on the subject table. AFTER must not be specified if view-name is also specified.

INSTEAD OF

Specifies that the trigger is an instead of trigger. The associated triggered action replaces the action against the subject view. Only one INSTEAD OF trigger is allowed for each type of operation on a given subject view. Db2 executes the triggered-action instead of the insert, update, or delete operation on the subject view.

INSTEAD OF must not be specified when table-name is also specified. The WHEN clause can not be specified for an INSTEAD OF trigger. FOR EACH ROW must be specified for an INSTEAD OF trigger.

REFERENCING

Specifies the correlation names for the transition variables and the table names for the transition tables. For the rows in the subject table that are modified by the triggering SQL operation (insert, delete, or update), a correlation name identifies the columns of a specific row. table-identifiers identify the complete set of affected rows. Transition variables with XML types cannot be referenced inside of a trigger. If the column of a transition table is referenced, the data type of the column cannot be XML.

Each row that is affected by the triggering SQL operation is available to the triggered action by qualifying column names with correlation-names that are specified as follows:

OLD ROW AS correlation-name

Specifies the correlation name that identifies the transition variables containing the values in the row prior to the triggering SQL operation.

NEW ROW AS correlation-name

Specifies the correlation name that identifies the transition variables containing the values in the row as modified by the triggering SQL operation and by any assignment statement in a before trigger that has already been executed.

If OLD and NEW are both specified, a reference to a transition variable must be qualified with the associated correlation name.

The complete set of rows that are affected by the triggering operation is available as a transition table to the triggered action. Specify a table-identifier to refer to the transition table as follows:

OLD_TABLE AS table-identifier

Specifies the name of a temporary table that identifies the values in the complete set of rows that are modified rows by the triggering SQL operation prior to any actual changes.

NEW_TABLE AS table-identifier

Specifies the name of a temporary table that identifies the values in the complete set of rows as modified by the triggering SQL operation and by any assignment statement in a before trigger that has already been executed.

Only one OLD and one NEW correlation-name can be specified for a trigger. Only one OLD_TABLE and one NEW_TABLE table-identifier can be specified for a trigger. All of the correlation-names and table-identifiers must be unique from one another.

Table 1 summarizes the allowable combinations of transition variables and transition tables that you can specify for the various trigger types. The OLD correlation-name and the OLD_TABLE table-identifier are valid only if the triggering event is either a delete operation or an update operation. For a delete operation, the OLD correlation-name captures the values of the columns in the deleted row, and the OLD_TABLE table-identifier captures the values in the set of deleted rows. For an update operation, the OLD correlation-name captures the values of the columns of a row before the update operation, and the OLD_TABLE table-identifier captures the values in the set of rows before the update operation.

The NEW correlation-name and the NEW_TABLE table-identifier are valid only if the triggering event is either an insert operation or an update operation. For both operations, the NEW correlation-name captures the values of the columns in the inserted or updated row and the NEW_TABLE table-identifier captures the values in the set of inserted or updated rows. For BEFORE triggers, the values of the updated rows include the changes from any assignment statements in the triggered action of BEFORE triggers.

Table 1. Allowable combinations of attributes in a trigger definition

Granularity	Activation time	Triggering SQL operation	Transition variables allowed1	Transition tables allowed1
FOR EACH ROW	BEFORE	DELETE	OLD	None
		INSERT	NEW	None
		UPDATE	OLD, NEW	None
	AFTER	DELETE	OLD	OLD_TABLE
		INSERT	NEW	NEW_TABLE
		UPDATE	OLD, NEW	OLD_TABLE, NEW_TABLE
	INSTEAD OF	DELETE	OLD	OLD_TABLE
		INSERT	NEW	NEW_TABLE
		UPDATE	OLD, NEW	OLD_TABLE, NEW_TABLE
FOR EACH STATEMENT	AFTER	DELETE	None	OLD_TABLE
		INSERT	None	NEW_TABLE
		UPDATE	None	OLD_TABLE, NEW_TABLE
Note: If a transition table or variable is referenced where it is not allowed, an error is returned.

A transition variable that has a character data type inherits the subtype and CCSID of the column of the subject table. During the execution of the triggered action, the transition variables are treated like host variables. Therefore, character conversion might occur. However, unlike a host variable, a transition variable can have the bit data attribute, and character conversion never occurs for bit data. A transition variable is considered to be bit data if the column of the table to which it corresponds is bit data.

You cannot modify a transition table; transition tables are read-only. Although a transition table does not inherit any edit or validation procedures from the subject table, it does inherit the encoding scheme and field procedures of the subject table.

The scope of each correlation-name and each table-identifier is the entire trigger definition.

trigger-granularity

FOR EACH ROW or FOR EACH STATEMENT

Specifies the conditions for which Db2 executes the triggered action.

FOR EACH ROW

Specifies that Db2 executes the triggered action for each row of the subject table that the triggering SQL operation modifies. If the triggering SQL operation does not modify any rows, the triggered action is not executed..

FOR EACH STATEMENT

Specifies that Db2 executes the triggered action only one time for the triggering operation. Even if the triggering operation does not modify or delete any rows, the triggered action is executed one time.

FOR EACH STATEMENT must not be specified for a BEFORE or INSTEAD OF trigger.

ALLOW DEBUG MODE, DISALLOW DEBUG MODE, or DISABLE DEBUG MODE

Specifies whether this version of the trigger can be run in debugging mode. The default is determined using the value of the CURRENT DEBUG MODE special register.

ALLOW DEBUG MODE

Specifies that this version of the trigger can be run in debugging mode. When ALLOW DEBUG MODE is in effect, a WLM environment must be available. If you do not specify WLM ENVIRONMENT FOR DEBUG MODE, Db2 uses the default WLM-established stored procedure address space specified at installation time.

DISALLOW DEBUG MODE

Specifies that this version of the trigger cannot be run in debugging mode. You can use an ALTER statement to change this option to ALLOW DEBUG MODE. When DISALLOW DEBUG MODE is in effect, a WLM environment must be available. If you do not specify WLM ENVIRONMENT FOR DEBUG MODE, Db2 uses the default WLM-established stored procedure address space specified at installation time.

DISABLE DEBUG MODE

Specifies that this version of the trigger can never be run in debugging mode.

This version of the trigger cannot be changed to specify ALLOW DEBUG MODE or DISALLOW DEBUG MODE after this version of the trigger has been created or altered to use DISABLE DEBUG MODE. To change this option, drop the trigger, and create it again using the option that you want. An alternative to dropping and recreating the trigger is to create a version of the trigger that uses the option that you want, and making that version the active version.

When DISABLE DEBUG MODE is in effect, the WLM ENVIRONMENT FOR DEBUG MODE is ignored.

QUALIFIER schema-name

Specifies the implicit qualifier that is used for unqualified object names that are referenced in the trigger body. For information about how the default for this option is determined, see Unqualified alias, index, JAR file, mask, permission, sequence, table, trigger, and view names.

ASUTIME

Specifies the total amount of processor time, in CPU service units, that a single invocation of this version of the trigger can run. The value is unrelated to the ASUTIME column of the resource limit specification table.

When you are debugging a trigger, setting a limit can be helpful in case the trigger gets caught in a loop. For information on service units, see z/OS MVS Initialization and Tuning Guide.

NO LIMIT

Specifies that there is no limit on the service units.

NO LIMIT is the default.

LIMIT integer

The limit on the number of CPU service units is a positive integer in the range 1–2147483647. If the trigger uses more service units than the specified value, Db2 cancels the trigger. The CPU cycles that are consumed by parallel tasks in a trigger do not contribute towards the specified ASUTIME LIMIT.

WLM ENVIRONMENT FOR DEBUG MODE name

Specifies the WLM (workload manager) application environment that is used by Db2 when debugging the trigger. The name of the WLM environment is an SQL identifier.

If you do not specify WLM ENVIRONMENT FOR DEBUG MODE, Db2 uses the default WLM-established stored procedure address space specified at installation time.

You must have the appropriate authority for the WLM application environment.

The WLM ENVIRONMENT FOR DEBUG MODE value is ignored when DISABLE DEBUG MODE is in effect.

CURRENT DATA YES or CURRENT DATA NO

Specifies whether to require data currency for read-only and ambiguous cursors when the isolation level of cursor stability is in effect. CURRENT DATA also determines whether block fetch can be used for distributed, ambiguous cursors.

CURRENT DATA YES

Specifies that data currency is required for read-only and ambiguous cursors. Db2 acquires page or row locks to ensure data currency. Block fetch is ignored for distributed, ambiguous cursors.

CURRENT DATA NO

Specifies that data currency is not required for read-only and ambiguous cursors. Block fetch is allowed for distributed, ambiguous cursors. Use of CURRENT DATA NO is not recommended if the trigger attempts to dynamically prepare and execute a DELETE WHERE CURRENT OF statement against an ambiguous cursor after that cursor is opened. You receive an error if your trigger attempts to use a DELETE WHERE CURRENT OF statement for any of the following cursors:

• A cursor that is using block fetch
• A cursor that is using query parallelism
• A cursor that is positioned on a row that is modified by this or another application process

CURRENT DATA NO is the default.

CONCURRENT ACCESS RESOLUTION

Specifies the whether processing uses only committed data or whether it will wait for commit or rollback of data that is in the process of being updated.

WAIT FOR OUTCOME

Specifies that processing will wait for the commit or rollback of data that is in the process of being updated.

USE CURRENTLY COMMITTED

Specifies that processing use the currently committed version of the data when data that is in the process of being updated is encountered. USE CURRENTLY COMMITTED is applicable on scans that access tables that are defined in universal table spaces with row or page level lock size.

When there is lock contention between a read transaction and an insert transaction, USE CURRENTLY COMMITTED is applicable to scans with isolation level CS or RS. Applicable scans include intent read scans for read-only and ambiguous queries and for updatable cursors. USE CURRENTLY COMMITTED is also applicable to scans initiated from WHERE predicates of UPDATE or DELETE statements and the subselect of INSERT statements.

When there is lock contention is between a read transaction and a delete transaction, USE CURRENTLY COMMITTED is applicable to scans with isolation level CS and when CURRENT DATA NO is specified.

DYNAMICRULES

Specifies the values that apply, at run time, for the following dynamic SQL attributes:

• The authorization ID that is used to check authorization
• The qualifier that is used for unqualified objects
• The source for application programming options that Db2 uses to parse and semantically verify dynamic SQL statements

In the context of a trigger, DYNAMICRULES also specifies whether dynamic SQL statements can include ALTER, CREATE, and DROP statements.

In addition to the value of the DYNAMICRULES clause, the run time environment of a trigger controls how dynamic SQL statements behave at run time. The combination of the DYNAMICRULES value and the run time environment determines the value for the dynamic SQL attributes. That set of attribute values is called the dynamic SQL statement behavior. The following values can be specified:

RUN

Specifies that dynamic SQL statements are to be processed using run behavior.

RUN is the default.

BIND

Specifies that dynamic SQL statements are to be processed using bind behavior.

See Authorization IDs and dynamic SQL for information on the effects of these options.

APPLICATION ENCODING SCHEME

Specifies the default encoding scheme for SQL variables in static SQL statements in the trigger body. The value is used for defining an SQL variable in a compound statement if the CCSID clause is not specified as part of the data type.

ASCII

Specifies that the data is encoded using the ASCII CCSIDs of the server.

EBCDIC

Specifies that the data is encoded using the EBCDIC CCSIDs of the server.

UNICODE

Specifies that the data is encoded using the Unicode CCSIDs of the server.

See ENCODING bind option for information about how the default for this option is determined.

WITH EXPLAIN or WITHOUT EXPLAIN

Specifies whether information will be provided about how SQL statements in the trigger will execute.

WITHOUT EXPLAIN

Specifies that information will not be provided about how SQL statements in the trigger will execute.

You can get EXPLAIN output for a statement that is embedded in a trigger that is specified using WITHOUT EXPLAIN by embedding the SQL statement EXPLAIN in the trigger body. Otherwise, the value of the EXPLAIN option applies to all explainable SQL statements in the trigger body, and to the fullselect portion of any DECLARE CURSOR statements.

WITHOUT EXPLAIN is the default.

WITH EXPLAIN

Specifies that information will be provided about how SQL statements in the trigger will execute. Information is inserted into the table owner. PLAN_TABLE. owner is the authorization ID of the owner of the trigger. Alternatively, the authorization ID of the owner of the trigger can have an alias as owner.PLAN_TABLE that points to the base table, PLAN_TABLE. owner must also have the appropriate SELECT and INSERT privileges on that table. PLAN_TABLE must have a base table and can have multiple aliases with the same table name, PLAN_TABLE, but have different schema qualifiers. It cannot be a view or a synonym and should exist before the CREATE statement is processed. In all inserts to owner.PLAN_TABLE, the value of QUERYNO is the statement number that is assigned by Db2.

The WITH EXPLAIN option also populates two optional tables, if they exist: DSN_STATEMNT_TABLE and DSN_FUNCTION_TABLE. DSN_STATEMNT_TABLE contains an estimate of the processing cost for an SQL statement. See Estimating the cost of SQL statements for more information. DSN_FUNCTION_TABLE contains information about function resolution. See Checking how Db2 resolves functions by using DSN_FUNCTION_TABLE for more information.

For more information about the EXPLAIN statement, including a description of the tables that are populated by the WITH EXPLAIN option, see EXPLAIN statement.

WITH IMMEDIATE WRITE or WITHOUT IMMEDIATE WRITE

Specifies whether immediate writes are to be done for updates that are made to group buffer pool dependent page sets or partitions. This option is only applicable for data sharing environments. The IMMEDWRITE subsystem parameter has no affect of this option. IMMEDWRITE bind option shows the implied hierarchy of the IMMEDWRITE bind option (which is similar to this trigger option) as it affects run time.

WITHOUT IMMEDIATE WRITE

Specifies that normal write activity is performed. Updated pages that are group buffer pool dependent are written at or before phase one of commit or at the end of abort for transactions that have been rolled back.

WITHOUT IMMEDIATE WRITE is the default.

WITH IMMEDIATE WRITE

Specifies that updated pages that are group buffer pool dependent are immediately written as soon as the buffer update completes. Updated pages are written immediately even if the buffer is updated during forward progress or during the rollback of a transaction. WITH IMMEDIATE WRITE might impact performance.

ISOLATION LEVEL RR, RS, CS, or UR

Specifies how far to isolate the trigger from the effects of other running applications. For information about isolation levels, see Choosing an ISOLATION option.

RR

Specifies repeatable read.

RS

Specifies read stability.

CS

Specifies cursor stability. CS is the default.

UR

Specifies uncommitted read.

OPTHINT string-constant

Specifies whether query optimization hints are used for static SQL statements that are contained within the body of the trigger.

string-constant is a character string of up to 128 bytes in length, which is used by the Db2 subsystem when searching the PLAN_TABLE for rows to use as input. The default value is an empty string, which indicates that the Db2 subsystem does not use optimization hints for static SQL statements.

Optimization hints are only used if optimization hints are enabled for your system. See Preparing to influence access paths for information about enabling optimization hints.

SQL PATH

Specifies the SQL path that Db2 uses to resolve unqualified user-defined types, functions, and procedure names in the body of the trigger. The default value is "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", and the value of the QUALIFIER option.

Schemas "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM" do not need to be explicitly specified. If any of these schemas is not explicitly specified, it is implicitly assumed at the beginning the SQL path, in the order listed.

Db2 calculates the length by taking each schema-name specified and removing any trailing blanks from it, adding two delimiters around it, and adding one comma after each schema name, except for the last one. The length of the resulting string cannot exceed the length of the CURRENT SCHEMA special register. If you do not specify the "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", schemas, they are not included in the length of the SQL path. If the total length of the SQL path exceeds the length of the CURRENT PATH special register, Db2 returns an error.

For more information, see:

• SQL path
• CURRENT SCHEMA special register
• CURRENT PATH special register

schema-name

Identifies a schema. Db2 does not verify that the schema exists when the ALTER statement is processed. The same schema name should not appear more than once in the list of schema names.

SYSPUBLIC must not be specified for the SQL path.

SYSTEM PATH

Specifies the schema names "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM".

SESSION_USER or USER

Specifies the value of the SESSION_USER or USER special register, which represents a maximum 8-byte (in EBCDIC) schema-name. At the time the ALTER statement is processed, this length is included in the total length of the list of schema names that is specified for the SQL PATH option.

If you specify SESSION_USER (or USER) in a list of schema names, do not use delimiters around the SESSION_USER (or USER) keyword.

RELEASE AT

Specifies when to release resources that the trigger uses: either at each commit point or when the trigger terminates.

COMMIT

Specifies that resources will be released at each commit point.

COMMIT is the default.

DEALLOCATE

Specifies that resources will be released only when the thread terminates. DEALLOCATE has no effect on dynamic SQL statements, which always use RELEASE AT COMMIT, with this exception: When you use the RELEASE AT DEALLOCATE clause and the WITH KEEP DYNAMIC clause, and the subsystem is installed with a value of YES for the field CACHE DYNAMIC SQL on installation panel DSNTIP8, the RELEASE AT DEALLOCATE option is honored for dynamic SELECT and data change statements.

Locks that are acquired for dynamic statements are held unit one of the following events occurs:

• The application process ends.
• The application process issues a PREPARE statement with the same statement identifier. (Locks are released at the next commit point).
• The statement is removed from the dynamic statement cache because the statement has not been used. (Locks are released at the next commit point.)
• An object that the statement is dependent on is dropped or altered, or a privilege that the statement needs is revoked. (Locks are released at the next commit point.)

RELEASE AT DEALLOCATE can increase the package or plan size because additional items become resident in the package or plan.

For more information, see Choosing a RELEASE option.

ROUNDING

Specifies the rounding mode for manipulation of DECFLOAT data. The default value is taken from the DEFAULT DECIMAL FLOATING POINT ROUNDING MODE in the application programming defaults module.

DEC_ROUND_CEILING

Specifies numbers are rounded towards positive infinity.

DEC_ROUND_DOWN

Specifies numbers are rounded towards 0 (truncation).

DEC_ROUND_FLOOR

Specifies numbers are rounded towards negative infinity.

DEC_ROUND_HALF_DOWN

Specifies numbers are rounded to nearest; if equidistant, round down.

DEC_ROUND_HALF_EVEN

Specifies numbers are rounded to nearest; if equidistant, round so that the final digit is even.

DEC_ROUND_HALF_UP

Specifies numbers are rounded to nearest; if equidistant, round up.

DEC_ROUND_UP

Specifies numbers are rounded away from 0.

DATE FORMAT ISO, EUR, USA, JIS, or LOCAL

Specifies the date format for result values that are string representations of date or time values. See String representations of datetime values for more information.

The default format is specified in the DATE FORMAT field of installation panel DSNTIP4 of the system where the routine is defined. You cannot use the LOCAL option unless you have a date exit routine.

DECIMAL(15), DECIMAL(31), DECIMAL(15,s), or DECIMAL(31,s)

Specifies the maximum precision that is to be used for decimal arithmetic operations. See Arithmetic with two decimal operands for more information. The default format is specified in the DECIMAL ARITHMETIC field of installation panel DSNTIPF of the system where the routine is defined. If the form pp.s is specified, s must be a number in the range 1–9. s represents the minimum scale that is to be used for division.

TIME FORMAT ISO, EUR, USA, JIS, or LOCAL

Specifies the time format for result values that are string representations of date or time values. See String representations of datetime values for more information.

The default format is specified in the TIME FORMAT field of installation panel DSNTIP4 of the system where the routine is defined. You cannot use the LOCAL option unless you have a date exit routine.

FOR UPDATE CLAUSE OPTIONAL or FOR UPDATE CLAUSE REQUIRED

Specifies whether the FOR UPDATE clause is required for a DECLARE CURSOR statement if the cursor is to be used to perform positioned updates.

FOR UPDATE CLAUSE REQUIRED

Specifies that a FOR UPDATE clause must be specified as part of the cursor definition if the cursor will be used to make positioned updates.

FOR UPDATE CLAUSE REQUIRED is the default.

FOR UPDATE CLAUSE OPTIONAL

Specifies that the FOR UPDATE clause does not need to be specified in order for a cursor to be used for positioned updates. The trigger body can include positioned UPDATE statements that update columns that the user is authorized to update.

The FOR UPDATE clause with no column list applies to static or dynamic SQL statements. Even if you do not use this clause, you can specify FOR UPDATE OF with a column list to restrict updates to only the columns that are identified in the FOR UPDATE clause and to specify the acquisition of update locks.

SECURED or NOT SECURED

Specifies that the trigger is to be changed to be secure or not secure. Changing a trigger between SECURED and NOT SECURED causes an implicit rebind of the trigger package. If an error is encountered during the implicit rebind of the trigger package, the ALTER TRIGGER statement returns the error.

SECURED

Specifies the trigger is considered secure.

SECURED must be specified for a trigger if its subject table is using row access control or column access control. SECURED must also be specified for a trigger that is created for a view and one or more of the underlying tables in the view definition is using row access control or column access control.

NOT SECURED

Specifies the trigger is considered not secure.

NOT SECURED must not be specified for a trigger whose subject table is using row access control or column access control. NOT SECURED must also not be specified for a trigger that is created for a view and one or more of the underlying tables in the view definition is using row access control or column access control.

BUSINESS_TIME SENSITIVE

Determines whether references to application-period temporal tables in both static and dynamic SQL statements are affected by the value of the CURRENT TEMPORAL BUSINESS_TIME special register.

YES

References to application-period temporal tables are affected by the value of the CURRENT TEMPORAL BUSINESS_TIME special register. YES is the default value.

NO

References to application-period temporal tables are not affected by the value of the CURRENT TEMPORAL BUSINESS_TIME special register.

For more information, see CURRENT TEMPORAL BUSINESS_TIME special register.

SYSTEM_TIME SENSITIVE

Determines whether references to system-period temporal tables in both static and dynamic SQL statements are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register.

YES

References to system-period temporal tables are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register. YES is the default value.

NO

References to system-period temporal tables are not affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register.

For more information, see CURRENT TEMPORAL SYSTEM_TIME special register.

ARCHIVE SENSITIVE

Determines whether references to archive-enabled tables in SQL statements are affected by the value of the SYSIBMADM.GET_ARCHIVE built-in global variable.

YES

References to archive-enabled tables are affected by the value of the SYSIBMADM.GET_ARCHIVE built-in global variable. YES is the default value.

NO

References to archive-enabled tables are not affected by the value of the SYSIBMADM.GET_ARCHIVE built-in global variable.

For related information, see GET_ARCHIVE built-in global variable.

APPLCOMPAT

Specifies the application compatibility level behavior for static SQL statements in the trigger body. The following Db2 function level values can be specified:

VvvRrMmmm

Compatibility with the behavior of the identified Db2 function level. For example, V12R1M510 specifies compatibility with the highest available Db2 12 function level. The equivalent function level or higher must be activated.

For the new capabilities that become available in each application compatibility level, see:

• SQL changes in Db2 13 application compatibility levels
• SQL changes in Db2 12 application compatibility levels

Tip: Extra program preparation steps might be required to increase the application compatibility level for applications that use data server clients or drivers to access Db2 for z/OS. For more information, see Setting application compatibility levels for data server clients and drivers.

V12R1

Compatibility with the behavior of Db2 12 function level 500. This value has the same result as specifying V12R1M500.

Values such as V11R1 and V10R1 for compatibility with previous Db2 releases are not supported for triggers.

CONCENTRATE STATEMENTS OFF or CONCENTRATE STATEMENTS WITH LITERALS

Specifies whether a dynamic SQL statement that specifies literal constants will be cached as a separate unique statement entry in the dynamic statement cache instead of sharing an existing statement in the cache. Dynamic SQL statements are eligible to share an existing statement in the cache if the new statement meets all of the conditions for sharing a cached version of the same dynamic statement except that the new statement specifies one or more literal constants that are different than the cached statement.

CONCENTRATE STATEMENTS OFF

Specifies that the dynamic SQL statement that specifies literal constants will be cached as a unique statement entry if it specifies one or more constants that are different than the cached version of the same dynamic statement. CONCENTRATE STATEMENTS OFF is the default dynamic statement caching behavior.

CONCENTRATE STATEMENTS WITH LITERALS

Specifies that the dynamic SQL statement that specifies literal constants will share a cached version of the same dynamic statement that is also prepared using the CONCENTRATE STATEMENTS WITH LITERALS option if the new dynamic statement meets all of the conditions for sharing the cached statement and the constants that are specified can be reused in place of the constants in the cached statement.

triggered-action

Specifies the action to be performed when the trigger is activated. The triggered-action is composed of one or more SQL statements and an optional condition that controls whether the statements are executed.

WHEN (search-condition)

Specifies a condition that evaluates to true, false, or unknown. The triggered SQL statements are executed only if the search-condition evaluates to true. If the WHEN clause is omitted, the associated SQL statements are always executed.

The WHEN clause must not be specified for an INSTEAD OF trigger.

SQL-trigger-body

Specifies a single SQL-control-statement, including a compound statement, or triggered-SQL-statement that is to be executed for the triggered-action. See SQL procedural language (SQL PL) for more information about defining SQL triggers.

SQL-control-statement

Specifies an SQL control statement, which can include nested control statements and other SQL statements specified in the syntax diagram for SQL-procedure-statement (SQL PL). The statements that can be specified for SQL-procedure-statement are subject to the syntax notes relating to triggers.

triggered-SQL-statement

Specifies an SQL statement that is the only statement in a trigger body.

.

Only certain SQL statements can be specified in the SQL-trigger-body.

The trigger body must not contain a statement that is not supported.

• A statement in the trigger body must not refer to host variables, undefined transition variables, or declared temporary tables.
• A statement in the trigger body must only refer to a table or view that is at the current server.
• A statement in the trigger body must only invoke a stored procedure or user-defined function that is at the current server. An invoked routine can, however, access a server other than the current server.
• A statement in the trigger body must not modify a column that is part of a BUSINESS_TIME period.
• The trigger body must not, directly or indirectly, issue a CALL statement for a procedure that contains one of the following statements:
  • COMMIT
  • CONNECT
  • RELEASE connection
  • RELEASE SAVEPOINT
  • ROLLBACK (without TO SAVEPOINT)
  • SAVEPOINT
  • SET CONNECTION
• The trigger body must not, directly or indirectly, issue a CALL statement for a procedure that is defined with the COMMIT ON RETURN option.
• If the trigger is a before trigger:
  • The trigger body must not contain a DELETE, INSERT, MERGE, REFRESH TABLE, TRUNCATE, UPDATE statement, or SELECT FROM data-change-statement.
  • The trigger body must not, directly or indirectly, invoke a routine containing one of the following statements:
    • ALTER
    • COMMENT
    • CREATE
    • DECLARE GLOBAL TEMPORARY TABLE
    • DELETE
    • DROP
    • EXCHANGE
    • GRANT
    • INSERT
    • LABEL
    • LOCK TABLE
    • MERGE
    • REFRESH TABLE
    • RENAME
    • REVOKE
    • TRUNCATE
    • UPDATE
    

A CREATE TRIGGER statement (or ALTER TRIGGER statement) cannot contain a hexadecimal graphic string (GX) constant.

The triggered action can refer to the values in the set of affected rows. This action is supported through the use of transition variables and transition tables.

All tables, views, aliases, sequences, roles, user-defined data types, user-defined functions, and procedures referenced in the triggered-action must exist at the current server when the trigger is created, or the version of the trigger is defined. The table or view that an alias refers to must also exist when the trigger is created, or the version of the trigger is defined.

Notes for ALTER TRIGGER (advanced)

Changing to an advanced trigger:

You cannot use an ALTER TRIGGER statement to change a basic trigger into an advanced trigger. You can drop the existing trigger you want to change using the DROP statement and then create an advanced trigger with a similar definition using the CREATE TRIGGER statement in Db2 12 with new function activated, or you can use the CREATE TRIGGER statement with the OR REPLACE clause. Alternatively, you can create an advanced trigger using a different schema. To define an advanced trigger, omit the MODE DB2SQL clause in the CREATE TRIGGER statement.

Changes are immediate:

Any changes that the ALTER TRIGGER statement causes to the definition of a trigger take effect immediately. The changed definition is used the next time that the trigger is activated.

Identifier resolution:

See SQL procedural language (SQL PL) for information on how names are resolved to columns, SQL variables, transition variables, or global variables within a trigger body.

If non-unique names are used for columns, SQL variables, transition variables, or global variables, qualify the non-unique names by using the table designator for columns, the label name for SQL variables, correlation name for transition variables, and schema for global variables.

Transition variables and transition tables:

The triggered action can refer to the values in the set of affected rows. This action is supported through the use of transition variables and transition tables.

A transition variable has the same name and data type as the corresponding column of the table that the trigger is defined on, and is nullable. A transition variable contains the value of a column in an affected row. A transition variable is qualified by a correlation name that identifies whether the reference is to the old value (before the update) or the new value (after the update). A transition variable can be referenced in the search condition, or in an SQL statement in the trigger body wherever a table would be allowed if the reference was made outside the body of a trigger. A new transition variable can be assigned a value in a before update or insert trigger. The values of the updated rows include the changes from any assignments to transition variables in the triggered action of a before trigger.

A transition table contains the complete set of affected rows with either the old values (before the update) or the new values (after the update). Transition tables are read only and can be referenced in the triggered action of an after or instead of trigger. Transition tables also use the name of columns of the subject table but have an associated table identifier that allows the complete set of affected rows to be treated as a table. Separate table identifiers can be specified for the old and new transition tables. Transition tables can be referenced in the triggered action in a search condition, or in an SQL statement in the trigger body wherever a variable would be allowed if the reference was made outside the body of a trigger. In addition, a transition table can be passed as an argument to a user-defined function or procedure specifying the TABLE keyword before the table identifier for the transition table. When the function or procedure is invoked, a table locator is passed for the transition table.

Altering a trigger from NOT SECURED to SECURED:

Typically, the security administrator will examine the data that is accessed by a trigger, ensure that it is secure, and grant the CREATE_SECURE_OBJECT privilege to the owner of the trigger. After the trigger is changed to SECURED, the security administrator will revoke the CREATE_SECURE_OBJECT privilege from the owner of the trigger.

The trigger is considered secure after the ALTER TRIGGER statement is executed. Db2 treats the SECURED attribute as an assertion that declares that the user has established an audit procedure for all activities in the trigger body. If a secure trigger references user-defined functions, Db2 assumes those functions are secure without validation. If those functions can access sensitive data, the user with SECADM authority needs to ensure that those functions are allowed to access that data and that an audit procedure is in place for all versions of those functions, and that all subsequent ALTER FUNCTION statements or changes to external packages are being reviewed by this audit process.

A trigger must be secure if its subject table is using row access control or column access control. SECURED must also be specified for a trigger that is created for a view and one or more of the underlying tables in the view definition is using row access control or column access control.

Altering a trigger from SECURED to NOT SECURED:

The ALTER TRIGGER statement returns an error if the subject table of the trigger is using row access control or column access control, or if the trigger is for a view and one or more of the underlying tables in the view definition is using row access control or column access control.

Altering obfuscated triggers:

Obfuscated triggers cannot be altered in any way.

Row access control and column access control that is not enforced for transition variables and transition tables:

If row access control or column access control is enforced for the subject table of the trigger, row permissions and column masks are not applied to the initial values of transition variables and transition tables. Row access control and column access control is enforced for the triggering table, but is ignored for transition variables and transition tables that are referenced in the body of the trigger body or are passed as arguments to user-defined functions that are invoked in the body of the trigger. To ensure that there are no security concerns for SQL statements accessing sensitive data in transition variables and transition tables in the trigger action, the trigger must be changed to use the SECURED option. If a trigger is not secure, row access control and column access control cannot be enforced for the triggering table.

Correspondence of trigger options to bind options

The following table lists options for CREATE TRIGGER and ALTER TRIGGER, and the corresponding options for the bind commands. See BIND and REBIND options for packages, plans, and services for more information about the effects of the bind options.

Table 2. Correspondence of trigger options to bind options

CREATE TRIGGER (advanced) or ALTER TRIGGER (advanced) option	bind commands option
APPLCOMPAT	APPLCOMPAT(V12R1)
APPLICATION ENCODING SCHEME	ENCODING(ASCII) ENCODING(EBCDIC) ENCODING(UNICODE)
ARCHIVE SENSITIVE	ARCHIVESENSITIVE(YES) ARCHIVESENSITIVE(NO)
BUSINESS_TIME SENSITIVE	BUSTIMESENSITIVE(YES) BUSTIMESENSITIVE(NO)
CURRENT DATA	CURRENTDATA(NO) CURRENTDATA(YES)
CONCENTRATE STATEMENTS OFF CONCENTRATE STATEMENTS WITH LITERALS	CONCENTRATESTMT(NO) CONCENTRATESTMT(YES)
CONCURRENT ACCESS RESOLUTION	CONCURRENTACCESSRESOLUTION (USECURRENTLYCOMMITTED) CONCURRENTACCESSRESOLUTION (WAITFOROUTCOME)
DYNAMICRULES	DYNAMICRULES(BIND) DYNAMICRULES(RUN)
ISOLATION LEVEL	ISOLATION(RR) ISOLATION(RS) ISOLATION(CS) ISOLATION(UR)
OPTHINT	OPTHINT
QUALIFIER	QUALIFIER
RELEASE	RELEASE(COMMIT) RELEASE(DEALLOCATE)
ROUNDING	ROUNDING(CEILING) ROUNDING(DOWN) ROUNDING(FLOOR) ROUNDING(HALFDOWN) ROUNDING(HALFEVEN) ROUNDING(HALFUP) ROUNDING(UP)
SQL PATH	PATH
SYSTEM_TIME SENSITIVE	SYSTEMTIMESENSITIVE(YES) SYSTEMTIMESENSITIVE(NO)
WITH OR WITHOUT EXPLAIN	EXPLAIN(YES) EXPLAIN(NO)
WITH OR WITHOUT IMMEDIATE WRITE	IMMEDWRITE(YES) IMMEDWRITE(NO)

Considerations for packages:

When certain attributes of a trigger are changed, the body of the trigger might be rebound or regenerated. However, there is no impact to existing packages that might cause the activation of the trigger, and such packages are not invalidated. No additional bind-related privileges are required for an implicit rebind or regeneration of the trigger body.

If an error is encountered during an implicit rebind, the ALTER TRIGGER statement fails and returns the error.

When execution of a package causes the activation of the trigger, the rebound or regenerated trigger package executes.

Table 3 summarizes when implicit rebind and regeneration occurs when specific options are changed. A value of Y in a row indicates that a rebind or regeneration will occur if the option is changed for a trigger.

Table 3. ALTER TRIGGER (advanced) options that result in rebind or regeneration when changed

ALTER TRIGGER (advanced) option	Change results in implicit rebind of the non-control statements of the body of the trigger ?	Change results in implicit regeneration of the entire body of the trigger ?
ALLOW DEBUG MODE, DISALLOW DEBUG MODE, or DISABLE DEBUG MODE	Y2	Y
APPLCOMPAT	N	N
APPLICATION ENCODING SCHEME	Y	Y
ARCHIVE SENSITIVE YES or NO	Y	Y
ASUTIME	N	N
BUSINESS_TIME SENSITIVE YES or NO	Y	Y
CONCENTRATE STATEMENTS OFF or WITH LITERALS	Y	N
CONCURRENT ACCESS RESOLUTION	Y	N
CURRENT DATA	Y	N
DATE FORMAT	Y	Y
DECIMAL	Y	Y
DYNAMICRULES BIND OR RUN	Y	N
FOR UPDATE CLAUSE OPTIONAL or FOR UPDATE CLAUSE REQUIRED	Y	N
ISOLATION LEVEL	Y	N
OPTHINT	Y	N
QUALIFIER	Y	N
RELEASE	Y	N
ROUNDING	Y	Y
SECURED OR NOT SECURED	Y	N
SQL PATH	Y	Y
SYSTEM_TIME SENSITIVE	Y	Y
TIME FORMAT	Y	Y
WITH EXPLAIN OR WITHOUT EXPLAIN	Y	N
WITH IMMEDIATE WRITE OR WITHOUT IMMEDIATE WRITE	Y	N
WLM ENVIRONMENT FOR DEBUG MODE	N	N
Note: When an implicit rebind of the non-control statements in the body of the trigger occurs, the QUALIFIER and SQL PATH options are not re-evaluated. The trigger package is rebound and regenerated if a value of ALLOW DEBUG MODE is changed to DISALLOW DEBUG MODE.

Application compatibility level considerations for trigger objects

The application compatibility level controls the adoption and use of new capabilities and enhancements. When an object is created or altered, two separate application compatibility levels are used: one to process the definition of the object, and the other for processing the SQL statements in the object body:

Object definition

The CURRENT APPLICATION COMPATIBILITY special register value is used to process the object definition, except for statements in the object body This application compatibility level is stored in the SYSENVIRONMENT.APPLCOMPAT column. You can use the environment ID value in the catalog definition of the object to locate the SYSENVIRONMENT row with the matching ENVID value. This application compatibility level can be changed when the object is regenerated.

Statements in the object body

The application compatibility level that is implicitly or explicitly specified with the APPLCOMPAT option of the CREATE or ALTER statement is used to process statements in the object body. This application compatibility level is stored in the SYSPACKAGE.APPLCOMPAT column for the package associated with the object definition.

Altering a trigger definition using a lower application compatibility level than the current definition

If the CURRENT APPLICATION COMPATIBILITY special register value is lower than the application compatibility level of the existing object definition, altering an object might result in an error even if the content of the ALTER statement is valid at the current level. The errors can occur when the existing object definition contains some functionality that requires a higher level than the CURRENT APPLICATION COMPATIBILITY special register setting.

Application compatibility levels for regenerating trigger objects

For ALTER statements that regenerate objects, the object definition is reprocessed using the application compatibility setting that is specified implicitly or explicitly by the USING APPLICATION COMPATIBILITY clause that follows the REGENERATE keyword. This application compatibility value replaces the existing value in the SYSENVIRONMENT.APPLCOMPAT column for the environment settings associated with the object definition.

If the USING APPLICATION COMPATIBILITY clause is not specified, the existing application compatibility value in the SYSENVIRONMENT.APPLCOMPAT column for the object definition is used to reprocess the text associated with the object definition.

The behavior of the statements in the body remains controlled by the value the existing APPLCOMPAT option of the object.

Considerations for the SYSENVIRONMENTS catalog table:

An ALTER statement that specifies new environment settings will result in a new row being added to the SYSENVIRONMENTS catalog table. The new row will be added even if an error is subsequently encountered during processing of the ALTER statement. Thus, a new SYSENVIRONMENTS row might be added even for an ALTER statement that fails.

Dependent objects:

A trigger is dependent on the triggering table and objects that are referenced in the trigger body.

Error handling in triggers:

A trigger can return errors like other SQL statements. Applications should be aware of the possible errors that can be expected when a trigger is invoked. Any exception SQLSTATE that is not handled within the trigger body (using a handler), results in the exception SQLSTATE being returned to the statement that caused the trigger to be activated. For example, if a SIGNAL statement (RESIGNAL statement or RAISE_ERROR function) raises a non-severe error in the SQL-trigger-body and the condition is not handled within the trigger body, SQLCODE -438 and the specified SQLSTATE will be returned. Other non-severe errors are returned with SQLCODE -723 and SQLSTATE 09000. Severe errors that occur during the execution of triggered SQL statements are returned with SQLCODE -901, -906, -911, and -913 and the corresponding SQLSTATE. Warnings are not returned.