CREATE TRIGGER

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.

Authorization

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

• The CREATEIN privilege on the schema
• SYSADM or SYSCTRL authority
• System DBADM

In defining a trigger on a table, the privilege set that is defined below must include SYSADM authority or each of the following:

• The SELECT privilege on the table on which the trigger is defined, if the REFERENCING clause is included in the trigger definition
• The SELECT privilege on any table or view in the search-condition of the triggered-action
• The necessary privileges to invoke the triggered SQL statements in the triggered action
• The authorization to define a trigger on the table, which 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
  • SYSCTRL authority
  • System DBADM authority
  If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

In defining a trigger on a view, the privilege set that is defined below must include SYSADM authority or each of the following:

• The SELECT privilege on the view on which the trigger is defined, if the REFERENCING clause is included in the trigger definition
• The SELECT privilege on any table or view in the search-condition of the triggered-action
• The necessary privileges to invoke the triggered SQL statements in the triggered action
• The authorization to define a trigger on the view, which must include at least one of the following:
  • Ownership of the view on which the trigger is defined
  • SYSCTRL authority
  • System DBADM authority

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. If the owner is a role, the implicit schema match does not apply and this role needs to include one of the previously listed conditions.

If the statement is dynamically prepared and is not running in a trusted context for which the ROLE AS OBJECT OWNER clause is specified, 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). If the schema name is not the same as the SQL authorization ID of the process, one of the following conditions must be met:

• The privilege set includes SYSADM or SYSCTRL authority.
• The SQL authorization ID of the process has the CREATEIN privilege on the schema.

If the SECURED option is specified, at least one of the following privileges is required:

• SECADM authority
• CREATE_SECURE_OBJECT privilege

Note: If the SEPARATE SECURITY subsystem parameter is set to NO, SYSADM authority has implicit SECADM authority.

Description

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.

The name is also used to create the trigger package; therefore, 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 must not begin with 'SYS' unless the name is 'SYSADM', or the schema name is 'SYSTOOLS' and the user who executes the CREATE statement has SYSADM or SYSCTRL privilege.

NO CASCADE 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.

NO CASCADE 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 STATEMENT must not 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 AS correlation-name

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

NEW AS correlation-name

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

The complete set of rows that are affected by the triggering operation is available to the triggered action by using table-identifiers that are specified 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 SET 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 updated rows.

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 SET 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.

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.

MODE DB2SQL

Specifies the mode of the trigger. MODE DB2SQL triggers are activated after all of the row operations have occurred.

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.

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 to be 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 the SQL statements that are to be executed for the triggered action.

triggered-SQL-statement

Specifies the SQL statements that can be specified as the only statement in a trigger body.

BEGIN ATOMIC triggered-SQL-statement,... END

Specifies a list of SQL statements that are to be executed for the triggered action. The statements are executed in the order in which they are specified.

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

Table 2. Allowable SQL statements

SQL statement	Trigger activation time
	BEFORE	AFTER	INSTEAD OF
CALL	X	X	X
DELETE (searched)		X	X
fullselect	X	X	X
INSERT		X	X
MERGE		X	X
REFRESH TABLE		X	X
SET transition variable	X
SIGNAL	X	X	X
TRUNCATE		X	X
UPDATE (serached)		X	X
VALUES	X	X	X

The statements in the triggered action have these restrictions:

• They must not refer to host variables, parameter markers, undefined transition variables, or declared temporary tables.
• They must only refer to a table or view that is at the current server.
• They 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.
• They must not contain a fullselect that refers to the subject table if the trigger is defined as BEFORE.
• They must not modify a column that is part of a BUSINESS_TIME period.

• 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, and must not reference a procedure or function that is defined as MODIFIES SQL DATA.
  • The trigger body must not, directly or indirectly, issue a CALL statement for a procedure containing one of the following statements:
    • ALTER
    • COMMENT
    • CREATE
    • DELETE
    • DROP
    • EXCHANGE
    • GRANT
    • LABEL
    • LOCK TABLE
    • MERGE
    • REFRESH TABLE
    • RENAME
    • REVOKE
    • TRUNCATE
    • UPDATE

A CREATE 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 version of the trigger is defined. The table or view that an alias refers to must also exist when the version of the trigger is defined.

Notes

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.

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 triggering SQL operation and activation time 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.

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, data type, and nullability attribute as the corresponding column of the table that the trigger is defined on. 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.

A transition variable or transition table is not affected after being returned from a procedure invoked from within a triggered action regardless of whether the corresponding parameter was defined in the CREATE PROCEDURE statement as IN, INOUT, or OUT.

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.

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 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 trigger transition variable in the trigger body.
• An XML column of a transition table cannot be referenced in the trigger body.

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.

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

Dropping a column from a subject table or a table referenced in the triggered-action:

A column cannot be dropped from a table for which a trigger is defined.

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 that is referenced in the body of a trigger, might have the following results:

• 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.

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.

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.
• version-id is an empty string.

DB2 creates the trigger package with the following initial attributes (some of these attributes can be modified using the REBIND TRIGGER PACKAGE command):

• ACTION(ADD)
• ARCHIVESENSITIVE(YES)
• BUSTIMESENSITIVE(YES)
• CURRENTDATA(NO)
• DBPROTOCOL(DRDA)
• DEGREE(1)
• DESCSTAT(value from the DESCSTAT subsystem parameter)
• DYNAMICRULES(BIND)
• ENABLE(*)
• ENCODING(0)
• EXPLAIN(NO)
• FLAG(I)
• ISOLATION(CS)
• REOPT(NONE)
• NODEFER(PREPARE)
• OPTHINT
• OWNER(authorization ID) or ROLE of appropriate
• PATH(path)
• RELEASE(COMMIT)
• ROUNDING(value from the CURRENT DECFLOAT ROUNDING MODE special register)
• SQLERROR(NOPACKAGE)
• SYSTIMESENSITIVE(YES)
• QUALIFIER(authorization ID)
• VALIDATE(BIND)

The values of OWNER, QUALIFIER, and PATH are set depending on whether the CREATE TRIGGER statement is embedded in a program or issued interactively. If the statement is embedded in a program, OWNER and QUALIFIER are the owner and qualifier of the package or plan. PATH is the value from the PATH bind option. If the statement is issued interactively, both OWNER and QUALIFIER are the SQL authorization ID. PATH is the value in the CURRENT PATH special register.

Multiple versions of a trigger package are not allowed. Use the REBIND command to explicitly rebind the trigger package. To specify the name of a trigger package for the bind commands, the trigger name must conform to the rules for an ordinary identifier.

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. Dropping the trigger drops the trigger package; dropping the subject table drops the trigger and the trigger package.

Considerations for SQL processor programs:

SQL processor programs, such as SPUFI, the 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.

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