CREATE TRIGGER statement (advanced trigger)

Invocation for CREATE 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 CREATE TRIGGER (advanced)

To create a new trigger in the implicit or explicit schema, the privilege set that is defined below must include at least one of the following:

• The CREATEIN 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)

To replace a trigger without specifying the VERSION clause, the privilege set that is defined below must include at least one of the following:

• Ownership of the trigger
• DATAACCESS authority and at least one of the following:
  • DROPIN and CREATEIN privilege on the schema
  • System DBADM authority
  • SYSCTRL authority
• SYSADM authority

Additionally, to create or replace an existing trigger for a table, the privilege set that is defined below must include at least one of the following:

• The TRIGGER privilege on the table on which the trigger is defined
• The ALTER privilege on the table on which the trigger is defined
• DBADM authority on the database that contains the table
• System DBADM authority
• SYSCTRL authority
• SYSADM authority
• Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

To add or replace a version of an existing trigger with the VERSION clause, the privilege set that is defined below must include at least one of the following:

• Ownership of the trigger
• DATAACCESS authority and
  • ALTERIN privilege on the schema
  • System DBADM authority
  • SYSCTRL authority
• SYSADM authority

Additional privileges might be needed for the following:

• To execute the body of the trigger, the owner of the trigger must have SYSADM authority, DATAACCESS authority, or each 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 that is referenced 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, either SECADM authority or the CREATE_SECURE_OBJECT privilege is needed.
  Note: If the SEPARATE SECURITY subsystem parameter is set to NO, SYSADM authority has implicit SECADM authority.
• 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 CREATE statement must have the required authority. For more information, see Managing authorizations for creation of stored procedures in WLM environments.
• Additional authorization might be required on the SYSDUMMYx tables depending on the content of the trigger definition. See SYSDUMMYx tables.

Privilege set: The privilege set is the privileges that are held by the SQL authorization ID of the process unless the process is within a trusted context and the ROLE AS OBJECT OWNER clause is specified. In that case, the privilege set is the privileges that are held by the role that is associated with the primary authorization ID of the process and the owner is that role.

Description for CREATE TRIGGER (advanced)

OR REPLACE

Specifies to replace or change the definition for the trigger, if one exists at the current server. This option is ignored if a definition for the trigger does not exist at the current server.

If the VERSION keyword is not specified, and the trigger does not yet exist, the trigger is created with the initial version of the trigger (V1).

If the VERSION keyword is not specified, and the trigger exists:

• The existing trigger must not be defined with more than a single version or with a single version for which the version id is not V1.
• Otherwise, the only version that currently exists is V1 (or the trigger is a basic trigger), the trigger is recreated as follows:
  • The existing definition is effectively dropped before the definition is replaced in the catalog.
  • Any existing comment is discarded.
  • The definition of the trigger (including the object owner and target table or view name) can change.
  • The timestamp associated with the trigger definition is updated.
  • The create time of the trigger is changed and the activation order of the trigger is not maintained.

If the VERSION keyword is specified and the specified version of the trigger exists, the version is effectively replaced in the catalog as if the ALTER TRIGGER statement had been issued with the REPLACE VERSION clause. The trigger owner is not changed. The create time of the trigger is not changed, and the statement does not affect the order of trigger execution.

When you replace a 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 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.

If the VERSION keyword is specified and the specified version of the trigger does not exist, the version is defined as if the ALTER TRIGGER statement had been issued with an ADD VERSION clause. The trigger owner is not changed. The create time of the trigger is not changed, and the statement does not affect the order of trigger execution.

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 for those options are used.

trigger-name

Names the trigger. The name, including the implicit or explicit schema name, must not identify a trigger that exists at the current server. However, specifying the name of an existing trigger is allowed in the following situations:

• When the OR REPLACE clause is specified to replace an existing trigger
• When the VERSION clause is specified to add or replace a version of an existing trigger

The name is also used to create the trigger package; therefore, if OR REPLACE is not specified, the name must also not identify a package that is already described in the catalog. The schema name becomes the collection-id of the trigger package. Although trigger-name can be specified as an ordinary or delimited identifier, the name should conform to the rules for an ordinary identifier. Refer to The implicitly created trigger package for additional information.

The schema name can be 'SYSTOOLS' if the privilege set includes the SYSCTRL privilege. Otherwise, the schema name must not begin with 'SYS' unless the schema name is 'SYSADM', 'SYSIBMADM', or 'SYSPROC'.

VERSION trigger-version-id

Specifies the version identifier for the version of the trigger that is to be defined or replaced. See Naming conventions in SQL for information about specifying trigger-version-id. You can use an ALTER TRIGGER statement with the ADD VERSION clause or the CREATE TRIGGER statement with the OR REPLACE clause to create additional versions of the trigger. V1 is the default version identifier. See Versions of a trigger for more information about the use of versions for triggers.

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.

ON table-name

Identifies the subject table of the BEFORE or AFTER trigger definition. The name must identify a base table that exists at the current server. It must not identify a materialized query table, a clone table, a temporary table, an auxiliary table, an alias, a synonym, a real-time statistics table, an accelerator-only table, a catalog table, or a directory table.

ON view-name

Identifies the subject view of the INSTEAD OF trigger definition. The name must identify a view that exists at the current server.

view-name must not specify a view where any of the following conditions are true:

• The view is defined with the WITH CASCADED CHECK option (a symmetric view)
• The view on which a symmetric view has been defined
• The view references data that is encoded with different encoding schemes or CCSID values
• The view has a column that is a ROWID column
• The view has a column that is based on an underlying column of any of the following types:
  • A LOB, XML, or ROWID column
  • An identity column
  • A security label column
  • A row change timestamp column
  • A row-begin column
  • A row-end column
  • A transaction start ID column
• The view has columns that have field procedures
• All of the underlying tables of the view are catalog tables
• All of the underlying tables of the view are created global temporary tables
• All of the underlying tables of the view are clone tables
• The view has other views that are dependent on it

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.

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 for the initial version of the trigger. 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, 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 CREATE 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 CREATE 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.

NOT SECURED or SECURED

Specifies whether the trigger is considered secure. NOT SECURED is the default.

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 applcompat-level

Specifies the application compatibility level behavior for static SQL statements in the trigger body. The default value is V12R1M500. The following applcompat-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.

For more information, see APPLCOMPAT bind option and APPL COMPAT LEVEL field (APPLCOMPAT subsystem parameter)

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.

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.

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.

WRAPPED obfuscated-statement-text

FL 500Specifies the encoded definition of the trigger. A CREATE TRIGGER statement can be encoded using the WRAP scalar function.

WRAPPED must not be specified on a static CREATE statement.

Notes for CREATE TRIGGER (advanced)

Owner privileges:

When an INSTEAD OF trigger is defined, the associated privilege (INSERT, UPDATE, or DELETE on the view) is given to the owner of the view. The owner is granted the privilege with the ability to grant that privilege to others. For more information about ownership of an object, see Authorization, privileges, permissions, masks, and object ownership.

Execution authorization:

The user executing the triggering SQL operation does not need authority to execute a SQL-trigger-body. An SQL-trigger-body executes using the authority of the owner of the trigger.

Activating a trigger:

Only insert, delete, or update operations can activate a trigger. The activation of a trigger might cause trigger cascading.Trigger cascading is the result of the activation of one trigger that executes SQL statements that cause the activation of other triggers or even the same trigger again. The triggered actions might also cause updates as a result of the original modification, which can result in the activation of additional triggers. With trigger cascading, a significant chain of triggers might be activated, causing a significant change to the database as a result of a single insert, delete, or update operation. The number of levels of nested trigger cascading is limited to 16.

Loading a table with the LOAD utility does not activate any triggers that are defined for the table if the SHRLEVEL NONE option is specified or accepted as the default. If the LOAD statement includes the SHRLEVEL CHANGE option, triggers are activated when loading a table with the LOAD utility.

Adding triggers to enforce constraints:

Adding a trigger on a table that already has rows in it will not cause the triggered-action to be executed. Thus, if the trigger is designed to enforce constraints on the data in the table, the data in the existing rows might not satisfy those constraints.

Multiple triggers:

Multiple triggers that have the same activation time and triggering event can be defined on a table. The triggers are activated in the order in which they were created. For example, the trigger that was created first is executed first; the trigger that was created second is executed second. If the OR REPLACE option is used to replace an existing trigger, the create time is changed and therefore might affect the order of trigger execution.

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 variable 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: either the old values (before the update) or the new values (after the update). A transition table is read-only, and can be referenced in the triggered-action of an after or instead of trigger. A column of a transition table has the same name, data type, and null attribute as the corresponding column of the table that the trigger is defined on. A transition table is referenced using an associated table identifier that allows the complete set of affected rows to be treated as a table. A transition table can be referenced in a 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. 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.

Considerations for table-locator-reference elements:

table-locator-reference must not be specified in the body of a trigger.

Considerations for implicitly hidden columns:

In the body of a trigger, a transition variable that corresponds to an implicitly hidden column can be referenced. A transition table, that corresponds to a table with an implicitly hidden column, includes that column as part of the transition table. Likewise, a transition variable will exist for the column that is defined as implicitly hidden. A transition variable that corresponds to an implicitly hidden column can be referenced in the body of a trigger.

Lines within the advanced trigger definition:

When an advanced trigger is created, information is retained on lines in the CREATE statement. Lines are determined by the presence of the new line control character.

In an advanced trigger, a new line control character is a special character that is used for a new line. The new line control characters for an advanced trigger include:

• Line feed
• New line
• Carriage return
• Carriage return, followed by a line feed
• Carriage return, followed by a new line

For more information about control characters, see Control characters.

Identifier resolution:

See SQL control statements for SQL routines for information on how names are resolved to columns, SQL variables, transition variables, or global variables in the 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.

Considerations for transition variables in AFTER and INSTEAD OF triggers:

Transition variables cannot be modified in an AFTER or INSTEAD OF trigger.

Considerations for INSTEAD OF triggers:

The addition of an INSTEAD OF trigger for a view affects the read-only characteristic of the view. If a read-only view has a dependency relationship with an INSTEAD OF trigger, the type of operation that is defined for the INSTEAD OF trigger defines whether the view is deletable, insertable, or updatable.

The creation of an INSTEAD OF trigger causes dependent packages, plans, and statements in the dynamic statement cache to be marked invalid if the view definition is not read-only.

The initial values for new transition variables or new transition table columns that are visible in an INSTEAD OF INSERT trigger are set as follows:

• If a value is explicitly specified for a column in the insert operation, the corresponding new transition variable is that explicitly specified value.
• If a value is not explicitly specified for a column in the insert operation or the DEFAULT clause is specified, the corresponding new transition variable is:
  • the default value of the underlying table column if the view column is updatable (without the INSTEAD OF trigger)
  • otherwise, the null value

  If a view column is not nullable and does not have a default, the value must be explicitly specified in the insert operation.

The initial values for new transition variables that are visible in an INSTEAD OF UPDATE trigger are set as follows:

• If a value is explicitly specified for a column in the update operation, the corresponding new transition variable is that explicitly specified value
• If the DEFAULT clause is explicitly specified for a column in the update operation, the corresponding new transition variable is:
  • the default value of the underlying table column if the view column is updatable (without the INSTEAD OF trigger)
  • otherwise, the null value

  If a view column is not nullable and does not have a default, the value must be explicitly specified in the update operation.

• Otherwise, the corresponding new transition variable is the existing value of the column in the row.

Considerations for a MERGE statement:

The MERGE statement can execute insert, delete, and update operations. The applicable triggers are activated for the MERGE statement for the data change operations that are executed.

Considerations for triggers that are defined on tables that contain XML columns:

Although a trigger can be defined on a table that contains an XML column:

• An XML column cannot be referenced with a transition variable in the trigger body. An SQL-procedure-statement cannot reference a transition variable that is an XML data type.
• An XML column of a transition table cannot be referenced in the trigger body. An SQL-procedure-statement cannot reference a column in a transition table that is an XML data type.

Creating a trigger with the SECURED option:

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 someone who requires the privileges to create a secured trigger. After the trigger is created, the security administrator will revoke the CREATE_SECURE_OBJECT privilege from the owner of the trigger.

The trigger is considered secure after the CREATE 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.

Creating a trigger with the NOT SECURED option:

The CREATE 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.

Row 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 created with the SECURED option. If a trigger is not secure, the CREATE TRIGGER statement returns an error.

Versions of a trigger:

The CREATE TRIGGER statement defines the initial version of the trigger. Only one version of the trigger is considered to be the active version of the trigger. You can define additional versions using the ADD VERSION clause of the ALTER TRIGGER statement, or the CREATE TRIGGER statement with the OR REPLACE clause and the VERSION clause where the trigger already exists. You can replace a version using the REPLACE VERSION clause of the ALTER PROCEDURE statement, or the CREATE PROCEDURE statement with the OR REPLACE clause and the VERSION clause, when the procedure version already exists. For all versions of a trigger, the following trigger attributes must be the same: trigger-activation-time, trigger-event, table or view name, and trigger-granularity. The content of the REFERENCING clause can differ for each version of the trigger.

Restrictions involving pending definition changes:

CREATE TRIGGER is not allowed if the trigger is defined on a table for which there are pending definition changes.

Considerations for the special plan, statement, and function tables for EXPLAIN:

You can create a trigger on PLAN_TABLE, DSN_STATEMNT_TABLE, or DSN_FUNCTION_TABLE. However, insert triggers that are defined on these tables are not activated when Db2 adds rows to the tables.

Dependent objects:

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

Adding a column to a subject table or a table referenced in the triggered-action:

If a column is added to the subject table after triggers have been defined, the following rules apply:

• If the trigger is an update trigger that was defined without an explicit list of column names, an update to the new column activates the trigger.
• If the subject table is referenced in the triggered-action, the new column is not accessible to the SQL statements until the trigger package is rebound.
• The OLD_TABLE and the NEW_TABLE transition tables contain the new column, but the column cannot be referenced unless the trigger is re-created. If the transition tables are passed to a user-defined function or a stored procedure, the user-defined function or stored procedure must be re-created with the new definition of the table (that is, the function or procedure must be dropped and re-created), and the package for the user-defined function or stored procedure must be rebound.

If a column is added to any table that is referenced in the triggered-action, the new column is not accessible to the SQL statements until the trigger package is rebound.

Altering the attributes of a column that the triggered action references:

If a column is altered in the table on which the trigger is defined (the subject table), the alter is processed, and the dependent trigger packages are invalidated.

Renaming the table for which the trigger is defined, or tables referenced in the triggered-action:

You cannot rename a table for which a trigger is defined (the subject table). Except for the subject table, you can rename any table to which the SQL statements in the triggered action refer. After renaming such a table, drop the trigger and then re-create the trigger so that it refers to the renamed table.

Dropping the table or view for which a trigger is defined:

If the table or view that a trigger is defined on is dropped, the trigger is also dropped.

Effects of dropping or revoking privileges on an object referenced in the triggered-action:

Dropping an object that is referenced in the body of a trigger, or revoking a privilege to an object referenced in the body of a trigger, may result in the following:

• Dropping any table, view, alias, or index that is referenced or used within the SQL statements in the triggered action causes the trigger and its package to be invalidated.
• Dropping a synonym that is referenced does not have any effect on the trigger or its package.
• Dropping a user-defined function that is referenced by the SQL statements in the triggered action is not allowed. An error occurs.
• Dropping a sequence that is referenced by the SQL statements in the triggered action is not allowed. An error occurs.
• Revoking a privilege on which the trigger depends causes the trigger and its package to be invalidated. If the appropriate privilege does not exist when the trigger package is rebound, the SQL statement that references the object fails.

Error handling in triggers:

A trigger can return errors, like other SQL statements. Applications need to account for 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 return of the exception SQLSTATE 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 corresponding SQLSTATE are 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, or -913, and the corresponding SQLSTATE. Warnings are not returned.

Triggers and global variables:

The content of a global variable that is referenced by a trigger is inherited from the triggering SQL operation (delete, insert, or update).

Special registers:

The values of the special registers that are used in the trigger body are determined when the trigger is activated. The value of the CURRENT PACKAGESET special register is set to the schema name of the trigger, and the values of the other special registers are inherited from the triggering SQL operation (insert, update, or delete). Special register values are saved when a trigger is activated and are restored on return from the trigger. The CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP special registers are not restored on return from the trigger.

Result sets for stored procedures:

If a trigger invokes a stored procedure that returns result sets, the application that activated the trigger cannot access those result sets.

Transaction isolation:

All of the statements in the SQL-trigger-body run under the isolation level that is in effect for the trigger.

Limiting processor time:

The Db2 resource limit facility allows you to specify the maximum amount of processor time for a dynamic, manipulative SQL statement such as SELECT or SQL data change statements. The execution of a trigger is counted as part of the triggering SQL statement. The ASUTIME trigger option can be used to specify such a limit.

Errors binding triggers:

When a CREATE TRIGGER statement is bound, the SQL statements within the triggered action might not be fully parsed. Syntax errors in those statements might not be caught until the CREATE TRIGGER statement is executed.

Characteristics of the package that is generated for a trigger:

The package that is associated with the trigger is named as follows:

• location is set to the value of the CURRENT SERVER special register.
• collection-id (schema) for the package is the same as the schema qualifier of the trigger.
• package-id is the same as the name of the trigger.

The package is generated using the bind options that correspond to the implicitly or explicitly specified trigger options. See Table 2 for more information. In addition to the corresponding bind options, the package is generated using the following bind options:

• FLAG(I)
• SQLERROR(NOPACKAGE)
• ENABLE(*)
• VALIDATE(BIND)

A trigger package becomes invalid if an object or privilege on which it depends is dropped or revoked. The next time that the trigger is activated, Db2 attempts to rebind the invalid trigger package. If the automatic rebind is unsuccessful, the trigger package remains invalid.

You cannot create another package from the trigger package, such as with the BIND COPY command. The only way to drop a trigger package is to drop the trigger or the subject table or view. Dropping the trigger drops the trigger package; dropping the subject table or view drops the trigger and the trigger package.

Each time that the trigger activates, the trigger package executes one or more times.

Considerations for SQL processor programs:

SQL processor programs, such as SPUFI, the Db2 command line processor, and DSNTEP2, might not correctly parse SQL statements in the triggered action that are ended with semicolons. These processor programs accept multiple SQL statements, each separated with a terminator character, as input. Processor programs that use a semicolon as the SQL statement terminator can truncate a CREATE TRIGGER statement with embedded semicolons and pass only a portion of it to Db2. Therefore, you might need to change the SQL terminator character for these processor programs. For information on changing the terminator character for SPUFI and DSNTEP2, see Setting the SQL terminator character in a SPUFI input data set and DSNTEP2 and DSNTEP4 sample programs.

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.

Alternative syntax and synonyms:

To provide compatibility with previous releases of Db2 or other products in the Db2 family, Db2 supports the following keywords:

• OLD TABLE as a synonym for OLD_TABLE
• NEW TABLE as a synonym for NEW_TABLE

Obfuscated statements:

FL 500A CREATE TRIGGER statement can be executed in obfuscated form. In an obfuscated statement, only the trigger name and the WRAPPED keyword are readable. The rest of the statement is encoded in such a way that it is not readable but can be decoded by a database server that supports obfuscated statements. The WRAP scalar function produces obfuscated statements. Any debug options that are specified when the trigger is created from an obfuscated statement are ignored.