CREATE TRIGGER statement (basic trigger)
The CREATE TRIGGER (basic) statement defines a basic trigger in a schema and builds a trigger package at the current server. Each time that the trigger activates, the trigger package executes one or more times.
FL 500For a description of the differences between basic and advanced triggers, see Triggers.
Invocation for CREATE TRIGGER (basic)
The 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 in effect. For more information, see Authorization IDs and dynamic SQL.
Authorization for CREATE TRIGGER (basic)
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
- Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)
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
- Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)
- System DBADM authority
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.
- SECADM authority
- CREATE_SECURE_OBJECT privilege
Syntax for CREATE TRIGGER (basic)
trigger-definition
trigger-activation-time
trigger-event
trigger-granularity
triggered-action
SQL-trigger-body
triggered-SQL-statement:
- 1 The statement is not allowed in an SQL-trigger-body for a BEFORE trigger.
- 2 The target of a SET-assignment-statement statement must be a transition variable.
option-list:
Description for CREATE TRIGGER (basic)
- 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 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'.
- 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,
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 STATEMENT must not be specified for an INSTEAD OF trigger.
In the view definition, the non-numeric columns of the outermost SELECT list of the fullselect must use the same encoding scheme.
- 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 assignment 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 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 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 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.
- MODE DB2SQL
- Indicates that a basic trigger is to be created.
- 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. Only certain SQL statements can be specified in the SQL-trigger-body.
- 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.
The trigger body must not contain a statement that is not supported. Table 2 shows the list of allowable SQL statements, which differs depending on whether the trigger is being defined as BEFORE, AFTER, or INSTEAD OF. An 'X' in the table indicates that the statement is valid.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.
- 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 (basic)
- 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 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.
- Identifier resolution:
- Names that are the same should be explicitly qualified. Qualifying a name clearly indicates
whether the name refers to a column, a transition variable, or a global variable. If the name is not
qualified or is qualified but is still ambiguous, the following rules describe how the name is
resolved. If correlation names are specified for both old and new transition variables, a reference
to a transition variable must be qualified with the associated correlation name. The name is
resolved by checking for a match in the following order:
- If the tables and views specified in the trigger body exist at the time the trigger is created, the name is first checked as a column name at the current server. If the name is found as a column name, but the privilege set that is used to issue the CREATE statement does not have the proper authority to access the table or view, an error is returned.
- If the name is not found as a column, the name is checked as a transition variable name.
- If the name is not found as a transition variable, the name is checked as a global variable.
- If the name is still not resolved, an error is returned.
- Considerations for transition variables in AFTER and INSTEAD OF triggers:
- Transition variables cannot be modified in an AFTER or INSTEAD OF trigger.
- Invalidation of packages:
- This statement might invalidate all packages that depend on target objects, and sometimes other related objects through cascading effects, depending on the clauses and keywords specified and other factors. For more information, see Changes that invalidate packages.
- Invalidation of cached dynamic SQL statements:
- This statement might invalidate cached dynamic SQL statements that depend on target objects, and sometimes other related objects through cascading effects. For more information, see Invalidation of cached dynamic statements.
- 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 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.
- 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 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.
- 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.
- 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
- 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 TRIGGER PACKAGE command to explicitly rebind the basic 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.
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:
- The SQL statements in the trigger body must conform to the behavior specified by the application compatibility option in effect. The application compatibility value used to process the trigger is recorded in the APPLCOMPAT column of the SYSIBM.SYSPACKAGE catalog table.
- 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.
Examples for CREATE TRIGGER (basic)
- Example 1
-
Create two triggers that track the number of employees that a company manages. The subject table is the EMPLOYEE table, and the triggers increment and decrement a column with the total number of employees in the COMPANY_STATS table. The tables have these columns:
- EMPLOYEE table: ID, NAME, ADDRESS, and POSITION
- COMPANY_STATS table: NBEMP, NBPRODUCT, and REVENUE
This example shows the use of transition variables in a row trigger to maintain summary data in another table.
Create the first trigger, NEW_HIRE, so that it increments the number of employees each time a new person is hired; that is, each time a new row is inserted into the EMPLOYEE table, increase the value of column NBEMP in table COMPANY_STATS by 1.CREATE TRIGGER NEW_HIRE AFTER INSERT ON EMPLOYEE FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1; END
Create the second trigger, FORM_EMP, so that it decrements the number of employees each time an employee leaves the company; that is, each time a row is deleted from the table EMPLOYEE, decrease the value of column NBEMP in table COMPANY_STATS by 1.CREATE TRIGGER FORM_EMP AFTER DELETE ON EMPLOYEE FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1; END
- Example 2
-
Create a trigger, REORDER, that invokes user-defined function ISSUE_SHIP_REQUEST to issue a shipping request whenever a parts record is updated and the on-hand quantity for the affected part is less than 10% of its maximum stocked quantity. User-defined function ISSUE_SHIP_REQUEST orders a quantity of the part that is equal to the part's maximum stocked quantity minus its on-hand quantity; the function also ensures that the request is sent to the appropriate supplier.
The parts records are in the PARTS table. Although the table has more columns, the trigger is activated only when columns ON_HAND and MAX_STOCKED are updated.
CREATE TRIGGER REORDER AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS REFERENCING NEW AS NROW FOR EACH ROW MODE DB2SQL WHEN (NROW.ON_HAND < 0.10 * NROW.MAX_STOCKED) BEGIN ATOMIC VALUES(ISSUE_SHIP_REQUEST(NROW.MAX_STOCKED - NROW.ON_HAND, NROW.PARTNO)); END
- Example 3
-
Repeat the scenario in Example 2 except use a fullselect instead of a VALUES statement to invoke the user-defined function. This example also shows how to define the trigger as a statement trigger instead of a row trigger. For each row in the transition table that evaluates to true for the WHERE clause, a shipping request is issued for the part.
CREATE TRIGGER REORDER AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS REFERENCING NEW_TABLE AS NTABLE FOR EACH STATEMENT MODE DB2SQL BEGIN ATOMIC SELECT ISSUE_SHIP_REQUEST(MAX_STOCKED - ON_HAND, PARTNO) FROM NTABLE WHERE (ON_HAND < 0.10 * MAX_STOCKED); END
- Example 4
- Assume that table EMPLOYEE contains column SALARY. Create a trigger, SAL_ADJ, that prevents an
update to an employee's salary that exceeds 20% and signals such an error. Have the error that is
returned with an SQLSTATE of '75001' and a description. This example shows that the SIGNAL statement
is useful for restricting changes that violate business rules.
CREATE TRIGGER SAL_ADJ AFTER UPDATE OF SALARY ON EMPLOYEE REFERENCING OLD AS OLD_EMP NEW AS NEW_EMP FOR EACH ROW MODE DB2SQL WHEN (NEW_EMP.SALARY > (OLD_EMP.SALARY * 1.20)) BEGIN ATOMIC SIGNAL SQLSTATE '75001' ('Invalid Salary Increase - Exceeds 20'); END
- Example 5
- Assume that the following statements create a table, WEATHER (which stores temperature values in
Fahrenheit), and a view, CELSIUS_WEATHER for users who prefer to work in Celsius instead of
Fahrenheit:
The following INSTEAD OF trigger is used on the CELSIUS_WEATHER view to convert Celsius values to Fahrenheit values and then insert the Fahrenheit value into the WEATHER table:CREATE TABLE WEATHER (CITY VARCHAR(25), TEMPF DECIMAL(5,2)); CREATE VIEW CELSIUS_WEATHER (CITY, TEMPC) AS SELECT CITY, (TEMPF-32)/1.8 FROM WEATHER;
CREATE TRIGGER CW_INSERT INSTEAD OF INSERT ON CELSIUS_WEATHER REFERENCING NEW AS NEWCW FOR EACH ROW MODE DB2SQL BEGIN ATOMIC INSERT INTO WEATHER VALUES (NEWCW.CITY, 1.8*NEWCW.TEMPC+32); END
- Example 6
- Consider an application that records and tracks changes to stock prices. The database contains
two tables, CURRENTQUOTE and QUOTEHISTORY. CURRENTQUOTE has columns SYMBOL, QUOTE, and STATUS.
QUOTEHISTORY has columns SYMBOL, QUOTE, and QUOTE_TIMESTAMP.
When the QUOTE column of CURRENTQUOTE is updated, the new quote must be copied, with a timestamp, to the QUOTEHISTORY table. Also, the STATUS column of CURRENTQUOTE must be updated to reflect whether the stock is:
1 Rising in value 2 At a new high for the year 3 Dropping in value 4 At a new low for the year 5 Steady in value CREATE TRIGGER statements that accomplish these tasks are as follows:
Trigger STOCK_STATUS sets the status:
CREATE TRIGGER STOCK_STATUS NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET NEWQUOTE.STATUS = CASE WHEN NEWQUOTE.QUOTE > (SELECT MAX(QUOTE) FROM QUOTEHISTORY WHERE SYMBOL = NEWQUOTE.SYMBOL AND YEAR(QUOTE_TIMESTAMP) = YEAR(CURRENT DATE) ) THEN 'High' WHEN NEWQUOTE.QUOTE < (SELECT MIN(QUOTE) FROM QUOTEHISTORY WHERE SYMBOL = NEWQUOTE.SYMBOL AND YEAR(QUOTE_TIMESTAMP) = YEAR(CURRENT DATE) ) THEN 'Low' WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE THEN 'Rising' WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE THEN 'Dropping' WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE THEN 'Steady' END; END
Trigger RECORD_HISTORY records changes in the QUOTEHISTORY table:
CREATE TRIGGER RECORD_HISTORY AFTER UPDATE OF QUOTE ON CURRENTQUOTE REFERENCING NEW AS NEWQUOTE FOR EACH ROW MODE DB2SQL BEGIN ATOMIC INSERT INTO QUOTEHISTORY VALUES (NEWQUOTE.SYMBOL, NEWQUOTE.QUOTE, CURRENT TIMESTAMP); END