CREATE TRIGGER statement (advanced)

(BMC.DB2.SPE2210)

The CREATE TRIGGER statement defines a new trigger.

Related topics

CREATE-TRIGGER-statement-basic

CDL-statements

CREATE TRIGGER schema.triggerName

Specifies the fully qualified name of the trigger to be created

VERSION versionNumber

Specifies the version of the trigger

OWNER triggerOwner

Specifies the owner of the trigger

CURRENT SCHEMA schema

Specifies the schema of the trigger

TABLE tableOwner.tableName

Specifies the triggering base table of the associated trigger or the name of the view for an INSTEAD OF trigger

TBQUALIFIED

Indicates whether the TABLE name was qualified in the CREATE TRIGGER statement

TBQUALIFIED parameter values

Value	Description
YES	TABLE name was qualified
NO	TABLE name was not qualified

ACTIVATE

Specifies whether to activate the trigger before or after the triggering event

ACTIVATE parameter values

Value	Description
BEFORE	Activates the trigger before the event
AFTER	Activates the trigger after the event
INSTEAD OF	Activates the trigger instead of updating, inserting into, or deleting from a view

GRANULARITY

Specifies whether Db2 executes the triggered action for each row or for each statement

GRANULARITY parameter values

Value	Description
ROW	Executes the action for each row
STATEMENT	Executes the action for each statement

SECURED

Specifies that the trigger is secured

NOT SECURED

Specifies that the trigger is not secured

ONEVENT

Specifies when Db2 executes the trigger

ONEVENT parameter values

Value	Description
INSERT	Db2 executes the trigger when an insertion occurs
DELETE	Db2 executes the trigger when a deletion occurs
UPDATE ALLCOLUMNS	Db2 executes the trigger when an update occurs, and updates all of the columns in the table
UPDATE COLUMNS columnNameList	Db2 executes the trigger when an update occurs, and updates a list of columns in columnNameList

REFOLDCORR correlationName

Specifies the OLD AS correlation name

REFNEWCORR correlationName

Specifies the NEW AS correlation name

REFOLDTB identifier

Specifies the OLD _TABLE AS identifier

REFNEWTB identifier

Specifies the NEW_TABLE AS identifier

COMMENT 'string'

Specifies the comment for the trigger

PATH (schemaList)

Specifies the path for the trigger

TRIGGERTEXT (triggerText)

Specifies the triggered action for the trigger

ACTIVE

Specifies whether to activate the version of the trigger

ACTIVE parameter values

Value	Description
NO	Version should not be recognized as the active version
YES	Activates the version

APPLCOMPAT

Specifies the package compatibility level for SQL

APPLCOMPAT parameter values

Value	Description
V12R1	SQL statements in the trigger are compatible with Db2 Version 12 Release 1
V13R1	SQL statements in the trigger are compatible with Db2 Version 13 Release 1
V12R1Mnnn	SQL statements in the trigger are compatible with Db2 Version 12 Release 1 Modification nnn
V13R1Mnnn	SQL statements in the trigger are compatible with Db2 Version 13 Release 1 Modification nnn
<DEFLT>	Default value <DEFLT> is a reserved keyword.

APPLICATION_ENCODING_SCHEME

Specifies the encoding scheme for the trigger

APPLICATION_ENCODING_SCHEME parameter values

Value	Description
ASCII	ASCII encoding
EBCDIC	EBCDIC encoding
UNICODE	Unicode encoding
<DEFLT>	Default value <DEFLT> is a reserved keyword.

ARCHIVE SENSITIVE

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

ARCHIVE SENSITIVE parameter values

Value	Description
NO	References are not affected
YES	References are affected

ASUTIME n

Specifies the number of CPU service units for the trigger

A value of 0 indicates that no limit exists on the number of units. Valid values for n are from 1 through 2147483647.

BUSINESS_TIME SENSITIVE

Specifies 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

BUSINESS_TIME SENSITIVE parameter values

Value	Description
NO	References are not affected
YES	References are affected

CONCURRENT_ACCESS_RESOLUTION

Specifies the concurrent access resolution option to use for statements in a package

CONCURRENT_ACCESS_RESOLUTION parameter values

Value	Description
<DEFLT>	Default value <DEFLT> is a reserved keyword.
WAIT	WAITFOROUTCOME option
USE	USECURRENTLYCOMMITTED option

CURRENT_DATA

Specifies whether to require data currency for cursors when the isolation level of cursor stability is in effect

CURRENT_DATA parameter values

Value	Description
NO	Data currency is not required
YES	Data currency is required

DATE_FORMAT

Specifies the date format for the trigger

DATE_FORMAT parameter values

Value	Description
<DEFLT>	Default value <DEFLT> is a reserved keyword.
EUR	IBM European standard (dd.mm.yyyy)
ISO	International Standards Organization format (yyyy-mm-dd)
JIS	Japanese Industrial Standards Christian era format (yyyy-mm-dd)
LOCAL	Installation-defined format
USA	IBM USA standard (mm/dd/yyyy)

DEBUG_MODE

Specifies whether the trigger is allowed for debugging

DEBUG_MODE parameter values

Value	Description
ALLOW	Allows debugging.
DISALLOW	Does not allow debugging
DISABLE	Disables debugging
<DEFLT>	Default value <DEFLT> is a reserved keyword.

DECIMAL

Specifies the rules for a decimal operation (DEC31 or DEC15)

DECIMAL parameter values

Value	Description
(x, y)	Decimal value (x) and the scale (y) The decimal value is either 31 or 15. Values for scale are from 1 through 9.
<DEFLT>	Default value for decimal value (x) <DEFLT> is a reserved keyword.

DYNAMICRULES

Specifies the values for the dynamic SQL attributes

DYNAMICRULES parameter values

Value	Description
RUN	Processes dynamic SQL statements by using standard attribute values
BIND	Processes dynamic SQL statements by using bind behavior

EXPLAIN

Specifies whether to add information about the package’s statements to the owner of the PLAN_TABLE table

EXPLAIN parameter values

Value	Description
NO	Does not add information
YES	Adds information

IMMEDIATE_WRITE

Specifies whether to immediately write updates that are made to dependent page sets for group buffer pools

IMMEDIATE_WRITE parameter values

Value	Description
NO	Normal write activity
YES	Immediately write updated pages

ISOLATION_LEVEL

Specifies the isolation level

ISOLATION_LEVEL parameter values

Value	Description
CS	Cursor stability
RR	Repeatable read
RS	Read stability
UR	Uncommitted read

OPTHINT

Specifies the value of the query optimization hint bind option

OPTHINT parameter values

Value	Description
'value'	Character string to be used by the optimizer when searching for input to Db2
<NONE>	Db2 should not use optimization hints for SQL statements

QUALIFIER

Specifies the implicit qualifier for the unqualified object names in the SQL statements

QUALIFIER parameter values

Value	Description
qualifier	Qualifier for the unqualified table, view, index, and alias names
<DEFLT>	Default value <DEFLT> is a reserved keyword.

RELEASE_AT

Specifies the release resources when the package was last bound or rebound

RELEASE_AT parameter values

Value	Description
COMMIT	COMMIT value
DEALLOCATE	DEALLOCATE value

ROUNDING

Specifies the rounding mode when the package is bound

ROUNDING parameter values

Value	Description
DEC_ROUND_CEILING	Rounds toward infinity
DEC_ROUND_DOWN	Rounds toward zero
DEC_ROUND_FLOOR	Rounds toward negative infinity
DEC_ROUND_HALF_DOWN	Round to the nearest digit If the digit is equidistant, it rounds down.
DEC_ROUND_HALF_EVEN	Round to the nearest digit If the digit is equidistant, it rounds to an even digit.
DEC_ROUND_HALF_UP	Rounds to the nearest digit If the digit is equidistant, round up.
DEC_ROUND_UP	Rounds away from zero
<DEFLT>	Default value <DEFLT> is a reserved keyword.

SQL_PATH

Specifies the SQL path that Db2 uses to resolve unqualified types and names in the trigger

SQL_PATH parameter values

Value	Description
DEFAULT	"SYSIBM", "SYSFUN", "SYSPROC", and the value of the QUALIFIER option
USER	Value of the SESSION_USER or USER special register
schemaName	Name of the schema
schemaNameList	List of schema names, separated by commas
<NONE>	No SQL path

SYSTEM_TIME SENSITIVE

Specifies 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

SYSTEM_TIME SENSITIVE parameter values

Value	Description
NO	References are not affected.
YES	References are affected.

TIME_FORMAT

Specifies the time format for values

TIME_FORMAT parameter values

Value	Description
<DEFLT>	Default value <DEFLT> is a reserved keyword.
EUR	IBM European standard (hh.mm.ss)
ISO	International Standards Organization format (hh.mm.ss)
JIS	Japanese Industrial Standards Christian era format (hh:mm:ss)
LOCAL	Installation-defined format
USA	IBM USA standard (hh:mm AM or PM)

FOR_UPDATE_CLAUSE

Specifies whether the FOR UPDATE clause is required or optional for a DECLARE CURSOR statement

FOR_UPDATE_CLAUSE parameter values

Value	Description
REQUIRED	Clause is required
OPTIONAL	Clause is optional

CONCENTRATE

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

CONCENTRATE parameter values

Value	Description
YES	Caches dynamic SQL statements as a separate unique statement entry in the dynamic statement cache
NO	Shares an existing statement in the dynamic statement cache

WLM_ENVIRONMENT

Specifies the workload manager (WLM) environment in which the trigger runs

WLM_ENVIRONMENT parameter values

Value	Description
<DEFLT>	Default value <DEFLT> is a reserved keyword.
WLMName	Name of the WLM environment.

CREATE TRIGGER statement (advanced)

On this page