The CREATE TRIGGER statement defines a trigger in the database. Triggers can be created to support general forms of integrity or business rules. A trigger defines a set of actions that are executed with, or triggered by, an INSERT, UPDATE, or DELETE statement.
Invocation
This statement can be embedded
in an application program or issued through the use of dynamic SQL
statements. It is an executable statement that can be dynamically
prepared only if DYNAMICRULES run behavior is in effect for the package
(SQLSTATE 42509).
Authorization
The privileges held by the
authorization ID of the statement must include at least one of the
following authorities:
- ALTER privilege on the table on which the BEFORE or AFTER trigger
is defined
- CONTROL privilege on the view on which the INSTEAD OF trigger
is defined
- Owner
of the view on which the INSTEAD OF trigger is defined
- ALTERIN privilege on the schema of the table or view on which
the trigger is defined
- DBADM authority
and one of:
- IMPLICIT_SCHEMA authority on the database, if the implicit or
explicit schema name of the trigger does not exist
- CREATEIN privilege on the schema, if the schema name of the trigger
refers to an existing schema
- DBADM authority
If the authorization ID of the statement
does not have DATAACCESS authority, the privileges (
excluding
group privileges) held by the authorization ID of the statement
must include all of the following authorities, as long as the trigger
exists:
- On the table on which the trigger is defined, if any transition
variables or tables are specified:
- SELECT privilege on the table on which the trigger is defined,
if any transition variables or tables are specified
- CONTROL privilege on the table on which the trigger is defined,
if any transition variables or tables are specified
- DATAACCESS authority
- On any table or view referenced in the triggered action condition:
- SELECT privilege on any table or view referenced in the triggered
action condition
- CONTROL privilege on any table or view referenced in the triggered
action condition
- DATAACCESS authority
- Necessary privileges to invoke the triggered SQL statements specified.
Group privileges are not considered for any table or view
specified in the CREATE TRIGGER statement.
To
replace an existing trigger, the authorization ID of the statement
must be the owner of the existing trigger (SQLSTATE 42501).
If the SECURED option is specified, the
privileges held by the authorization ID of the statement must additionally
include SECADM or CREATE_SECURE_OBJECT authority (SQLSTATE 42501).
Syntax
>>-CREATE--+------------+--TRIGGER--trigger-name---------------->
'-OR REPLACE-'
.-NO CASCADE-.
>--+-+------------+--BEFORE-+--| trigger-event |---------------->
+-AFTER------------------+
'-INSTEAD OF-------------'
>--ON--+-table-name-+------------------------------------------->
'-view-name--'
>--+----------------------------------------------------------------+-->
| .-----------------------------------------------. |
| V (1) (2) .-AS-. | |
'-REFERENCING----------------+-OLD--+----+--correlation-name-+-+-'
| .-AS-. |
+-NEW--+----+--correlation-name-+
| .-AS-. |
+-OLD TABLE--+----+--identifier-+
| .-AS-. |
'-NEW TABLE--+----+--identifier-'
.-NOT SECURED-.
>--+-FOR EACH ROW-------------+--+-------------+---------------->
| (3) | '-SECURED-----'
'-------FOR EACH STATEMENT-'
>--| triggered-action |----------------------------------------><
trigger-event
.-OR--------------------------------------.
V (4) |
|----+-INSERT--------------------------+-----+------------------|
+-DELETE--------------------------+
'-UPDATE--+---------------------+-'
| .-,-----------. |
| V | |
'-OF----column-name-+-'
triggered-action
|--+------------------------------------+----------------------->
| (5) |
'-------WHEN--(--search-condition--)-'
>--+--------+--| SQL-procedure-statement |----------------------|
'-label:-'
SQL-procedure-statement
|--+-CALL----------------------------------------------+--------|
| (6) |
+-Compound SQL (compiled)---------------------------+
+-Compound SQL (inlined)----------------------------+
+-FOR-----------------------------------------------+
+-+-----------------------------------+--fullselect-+
| | .-,-----------------------. | |
| | V | | |
| '-WITH----common-table-expression-+-' |
+-GET DIAGNOSTICS-----------------------------------+
+-IF------------------------------------------------+
+-INSERT--------------------------------------------+
+-ITERATE-------------------------------------------+
+-LEAVE---------------------------------------------+
+-MERGE---------------------------------------------+
+-searched-delete-----------------------------------+
+-searched-update-----------------------------------+
+-SET Variable--------------------------------------+
+-SIGNAL--------------------------------------------+
'-WHILE---------------------------------------------'
Notes:
- OLD and NEW can only be specified once each.
- OLD TABLE and NEW TABLE can only be specified once each,
and only for AFTER triggers or INSTEAD OF triggers.
- FOR EACH STATEMENT may not be specified for BEFORE triggers
or INSTEAD OF triggers.
- A trigger event must not be specified more than once for
the same operation. For example, INSERT OR DELETE is allowed, but
INSERT OR INSERT is not allowed.
- WHEN condition may not be specified for INSTEAD OF triggers.
- A
compound SQL (compiled) statement cannot be specified if the trigger
definition includes a REFERENCING OLD TABLE clause or a REFERENCING
NEW TABLE clause. A compound SQL (compiled) statement also cannot
be specified for a trigger definition in a partitioned database environment.
Description
- OR REPLACE
- Specifies to replace
the definition for the trigger if one exists at the current server.
The existing definition is effectively dropped before the new definition
is replaced in the catalog. This option is ignored if a definition
for the trigger does not exist at the current server. This option
can be specified only by the owner of the object.
- trigger-name
- Names the trigger. The name, including the implicit or explicit
schema name, must not identify a trigger already described in the
catalog (SQLSTATE 42710). If a two-part name is specified, the schema
name cannot begin with 'SYS' (SQLSTATE 42939).
- NO CASCADE BEFORE
- Specifies that the associated triggered action is to be applied
before any changes caused by the actual update of the subject table
are applied to the database. It also specifies that the triggered
action of the trigger will not cause other triggers to be activated.
- AFTER
- Specifies that the associated triggered action is to be applied
after the changes caused by the actual update of the subject table
are applied to the database.
- INSTEAD OF
- Specifies that the associated triggered action replaces the action
against the subject view. Only one INSTEAD OF trigger is allowed for
each kind of operation on a given subject view (SQLSTATE 428FP).
- trigger-event
- Specifies that the triggered action
associated with the trigger is to be executed whenever one of the
events is applied to the subject table or subject view. Any combination
of the events can be specified, but each event (INSERT, DELETE, and
UPDATE) can only be specified once (SQLSTATE.42613). If multiple events
are specified, the triggered action must be a compound SQL (compiled)
statement (SQLSTATE 42601).
- INSERT
- Specifies that the triggered action associated with the trigger
is to be executed whenever an INSERT operation is applied to the subject
table or subject view.
- DELETE
- Specifies that the triggered action associated with the trigger
is to be executed whenever a DELETE operation is applied to the subject
table or subject view.
- UPDATE
- Specifies that the triggered action associated with the trigger
is to be executed whenever an UPDATE operation is applied to the subject
table or subject view, subject to the columns specified or implied.
If the optional column-name list
is not specified, every column of the table or view is implied. Therefore,
omission of the column-name list implies
that the trigger will be activated by the update of any column of
the table or view. - OF column-name,...
- Each column-name specified must be a
column of the base table (SQLSTATE 42703). If the trigger is a BEFORE
trigger, the column-name specified cannot
be a generated column other than the identity column (SQLSTATE 42989).
No column-name can appear more than once
in the column-name list (SQLSTATE 42711).
The trigger will only be activated by the update of a column that
is identified in the column-name list. This
clause cannot be specified for an INSTEAD OF trigger (SQLSTATE 42613).
- ON
-
- table-name
- Designates the subject table of the BEFORE trigger or AFTER trigger
definition. The name must specify a base table or an alias that resolves
to a base table (SQLSTATE 42704 or 42809). The name must not specify
a catalog table (SQLSTATE 42832), a materialized query table (SQLSTATE
42997), a created temporary table, a declared
temporary table (SQLSTATE 42995), or a nickname (SQLSTATE 42809).
- view-name
- Designates the subject view of the INSTEAD OF trigger definition.
The name must specify an untyped view or an alias that resolves to
an untyped view with no columns of type XML (SQLSTATE 42704
or 42809). The name must not specify a catalog view (SQLSTATE 42832).
The name must not specify a view that is defined using WITH CHECK
OPTION (a symmetric view), or a view on which a symmetric view has
been defined, directly or indirectly (SQLSTATE 428FQ).
- NOT SECURED or SECURED
- Specifies whether the trigger is considered secure. The default
is NOT SECURED.
- NOT SECURED
- Specifies the trigger is considered not secure.
- SECURED
- Specifies the trigger is considered secure. SECURED must be specified
for a trigger whose subject table is a table on which row level or
column level access control has been activated (SQLSTATE 428H8). Similarly,
SECURED must be specified for a trigger that is created on a view
and one or more of the underlying tables in that view definition has
row level or column level access control activated (SQLSTATE 428H8).
- REFERENCING
- Specifies the correlation names for the transition variables and
the table names for the transition tables. Correlation names
identify a specific row in the set of rows affected by the triggering
SQL operation. Table names identify the complete set of affected
rows. Each row affected by the triggering SQL operation is available
to the triggered action by qualifying columns with correlation-names specified
as follows.
- OLD AS correlation-name
- Specifies a correlation name which identifies the row state before
the triggering SQL operation.
- NEW AS correlation-name
- Specifies a correlation name which identifies the row state as
modified by the triggering SQL operation and by any SET statement
in a BEFORE trigger that has already executed.
The complete set of rows affected by the triggering
SQL operation is available to the triggered action by using a temporary
table name specified as follows.
- OLD TABLE AS identifier
- Specifies
a temporary table name which identifies the set of affected rows before
the triggering SQL operation. If the trigger event is INSERT, the
temporary table is empty.
- NEW TABLE AS identifier
- Specifies
a temporary table name which identifies the affected rows as modified
by the triggering SQL operation and by any SET statement in a BEFORE
trigger that has already executed. If the trigger event is DELETE,
the temporary table is empty.
The following rules apply to the REFERENCING
clause:
- None of the OLD and NEW correlation names and the OLD TABLE and
NEW TABLE names can be identical (SQLSTATE 42712).
- Only one OLD and one NEW correlation-name may
be specified for a trigger (SQLSTATE 42613).
- Only one OLD TABLE and one NEW TABLE identifier may
be specified for a trigger (SQLSTATE 42613).
- OLD TABLE or NEW TABLE identifiers cannot be defined in a BEFORE
trigger (SQLSTATE 42898).
- A
NEW transition variable can only be the target of an assignment in
a BEFORE trigger. Otherwise, transition variables cannot be the target
of an assignment (SQLSTATE 42703 or 42987).
- OLD or NEW correlation names cannot be defined in a FOR EACH STATEMENT
trigger (SQLSTATE 42899).
- Transition tables cannot be modified (SQLSTATE 42807).
- The total of the references to the transition table columns and
transition variables in the triggered-action cannot exceed the limit
for the number of columns in a table or the sum of their lengths cannot
exceed the maximum length of a row in a table (SQLSTATE 54040).
- The scope of each correlation-name and
each identifier is the entire trigger definition.
- If
the triggered-action includes a compound SQL (compiled) statement:
- OLD TABLE or NEW TABLE identifiers cannot be defined.
- If the operation is a DELETE operation, OLD correlation-name captures
the value of the deleted row. If it is an UPDATE operation, it captures
the value of the row before the UPDATE operation. For an insert operation,
OLD correlation-name captures null values
for each column of a row.
- For an insert operation or an update operation, the value of NEW
captures the new state of the row as provided by the original operation
and as modified by any BEFORE trigger that has executed to this point.
For a delete operation, NEW correlation-name captures
null values for each column of a row. In a BEFORE DELETE trigger,
any non-null values assigned to the new transition variables persist
only within the trigger where the assignment occurred.
- If
the triggered-action does not include a compound SQL (compiled) statement:
- The OLD correlation-name and the OLD
TABLE identifier can only be used if the
trigger event is either a DELETE operation or an UPDATE operation
(SQLSTATE 42898). If the operation is a DELETE operation, OLD correlation-name captures
the value of the deleted row. If it is an UPDATE operation, it captures
the value of the row before the UPDATE operation. The same applies
to the OLD TABLE identifier and the set
of affected rows.
- The NEW correlation-name and the NEW
TABLE identifier can only be used if the
trigger event is either an INSERT operation or an UPDATE operation
(SQLSTATE 42898). In both operations, the value of NEW captures the
new state of the row as provided by the original operation and as
modified by any BEFORE trigger that has executed to this point. The
same applies to the NEW TABLE identifier and
the set of affected rows.
- FOR EACH ROW
- Specifies that the triggered action is to be applied once for
each row of the subject table or subject view that is affected by
the triggering SQL operation.
- FOR EACH STATEMENT
- Specifies that the triggered action is to be applied only once
for the whole statement. This type of trigger granularity cannot be
specified for a BEFORE trigger or an INSTEAD OF trigger (SQLSTATE
42613). If specified, an UPDATE or DELETE trigger is activated, even
if no rows are affected by the triggering UPDATE or DELETE statement.
- triggered-action
- Specifies the action to be performed when a trigger is activated.
A triggered action is composed of an SQL-procedure-statement and
by an optional condition for the execution of the SQL-procedure-statement.
Trigger
event predicates can be used anywhere in the triggered action of a
CREATE TRIGGER statement that uses a compound SQL (compiled) statement
as the SQL-procedure-statement.
- WHEN
-
- (search-condition)
- Specifies a condition that is true, false, or unknown. The search-condition provides
a capability to determine whether or not a certain triggered action
should be executed. The associated action is performed only if the
specified search condition evaluates as true. If the WHEN clause is
omitted, the associated SQL-procedure-statement is
always performed.
The WHEN clause cannot be specified for INSTEAD
OF triggers (SQLSTATE 42613).
A reference to a transition variable
with an XML data type can be used only in a VALIDATED predicate.
- label:
- Specifies the label for an SQL procedure statement. The label
must be unique within a list of SQL procedure statements, including
any compound statements nested within the list. Note that compound
statements that are not nested can use the same label. A list of SQL
procedure statements is possible in a number of SQL control statements.
Only the FOR statement, WHILE statement, and the compound SQL
statement can include a label.
- SQL-procedure-statement
- Specifies the SQL statement that is to be part of the triggered action. A searched update,
searched delete, insert, or merge operation on nicknames inside compound SQL is not supported.
The
triggered action of a BEFORE trigger on a column of type XML can invoke the XMLVALIDATE function
through a SET statement, leave values of type XML unchanged, or assign them to NULL using a SET
statement.
The SQL-procedure-statement must not contain a statement that is not
supported (SQLSTATE 42987).
The SQL-procedure-statement cannot reference an undefined
transition variable (SQLSTATE 42703), a federated object (SQLSTATE 42997), or a declared temporary
table (SQLSTATE 42995). or the start and end columns of the BUSINESS_TIME period (SQLSTATE
42808).
The
SQL-procedure-statement in a BEFORE trigger cannot:
- Contain any INSERT, DELETE, or
UPDATE operations, nor invoke any routine defined with MODIFIES SQL DATA, if it is not a compound
SQL (compiled).
- Contain any DELETE or UPDATE
operations on the trigger subject table, nor invoke any routine containing such operations, if it is
a compound SQL (compiled).
- Reference a materialized query table defined with REFRESH IMMEDIATE (SQLSTATE 42997)
- Reference a generated column other than the identity column in the NEW transition variable
(SQLSTATE 42989).
A triggered action cannot include an operation that does
an insert or update to a column-organized table.
Notes
- Adding a trigger to a table that already has rows in it will not
cause any triggered actions to be activated. Thus, if the trigger
is designed to enforce constraints on the data in the table, those
constraints may not be satisfied by the existing rows.
- If the events for two triggers occur simultaneously (for example,
if they have the same event, activation time, and subject tables),
then the first trigger created is the first to execute. If
the OR REPLACE option is used to replace a previously created trigger,
the create time is changed and therefore could affect the order of
trigger execution.
- 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 specified without
an explicit column list, then an update to the new column will cause
the activation of the trigger.
- The column will not be visible in the triggered action of any
previously defined trigger.
- The OLD TABLE and NEW TABLE transition tables will not contain
this column. Thus, the result of performing a "SELECT *" on a transition
table will not contain the added column.
- If a column is added to any table referenced in a triggered action,
the new column will not be visible to the triggered action.
- If an object referenced in the trigger body
does not exist or is marked invalid, or the definer temporarily doesn't
have privileges to access the object, and if the database configuration
parameter auto_reval is not set to DISABLED,
then the trigger will still be created successfully. The trigger will
be marked invalid and will be revalidated the next time it is invoked.
- The result of a fullselect specified in a SQL-procedure-statement is
not available inside or outside of the trigger.
- A procedure called within a triggered compound statement must
not issue a COMMIT or a ROLLBACK statement (SQLSTATE 42985).
- A procedure that contains a reference to a nickname in a searched
UPDATE statement, a searched DELETE statement, or an INSERT statement
is not supported (SQLSTATE 25000).
- Table access restrictions:: If a procedure is defined
as READS SQL DATA or MODIFIES SQL DATA, no statement in the procedure
can access a table that is being modified by the compound statement
that invoked the procedure (SQLSTATE 57053). If the procedure is defined
as MODIFIES SQL DATA, no statement in the procedure can modify a table
that is being read or modified by the compound statement that invoked
the procedure (SQLSTATE 57053).
- A BEFORE DELETE trigger defined on a table involved in a cycle
of cascaded referential constraints should not include references
to the table on which it is defined or any other table modified by
cascading during the evaluation of the cycle of referential integrity
constraints. The results of such a trigger are data dependent and
therefore may not produce consistent results.
In its simplest
form, this means that a BEFORE DELETE trigger on a table with a self-referencing
referential constraint and a delete rule of CASCADE should not include
any references to the table in the triggered-action.
- The creation of a trigger causes certain packages to be marked
invalid:
- If an UPDATE trigger without an explicit column list is created,
then packages with an update usage on the target table or view are
invalidated.
- If an UPDATE trigger with a column list is created, then packages
with update usage on the target table are only invalidated if the
package also has an update usage on at least one column in the column-name list
of the CREATE TRIGGER statement.
- If an INSERT trigger is created, packages that have an insert
usage on the target table or view are invalidated.
- If a delete trigger is created, packages that have a delete usage
on the target table or view are invalidated.
- A package remains invalid until the application program is explicitly
bound or rebound, or it is executed and the database manager automatically
rebinds it.
- Inoperative triggers: An inoperative
trigger is a trigger that is no longer available and is therefore
never activated. A trigger becomes inoperative if:
- a privilege that the creator of the trigger is required to have
for the trigger to execute is revoked
- an object such as a table, view or alias, upon which the triggered
action is dependent, is dropped
- a view, upon which the triggered action is dependent, becomes
inoperative
- an alias that is the subject table of the trigger is dropped.
In practical terms, an inoperative trigger is one in which
a trigger definition has been dropped as a result of cascading rules
for DROP or REVOKE statements. For example, when a view is dropped,
any trigger with an SQL-procedure-statement that contains a reference to that view is made
inoperative.
When a trigger is made inoperative, all packages
with statements performing operations that were activating the trigger
will be marked invalid. When the package is rebound (explicitly or
implicitly) the inoperative trigger is completely ignored. Similarly,
applications with dynamic SQL statements performing operations that
were activating the trigger will also completely ignore any inoperative
triggers.
The trigger name can still be specified in the DROP
TRIGGER and COMMENT ON TRIGGER statements.
An inoperative
trigger may be re-created by issuing a CREATE TRIGGER statement using
the definition text of the inoperative trigger. This trigger definition
text is stored in the TEXT column of the SYSCAT.TRIGGERS catalog view.
Note that there is no need to explicitly drop the inoperative trigger
in order to re-create it. Issuing a CREATE TRIGGER statement with
the same trigger-name as an inoperative
trigger will cause that inoperative trigger to be replaced with a
warning (SQLSTATE 01595).
Inoperative triggers are indicated
by an X in the VALID column of the SYSCAT.TRIGGERS catalog view.
- Errors executing triggers: Errors that occur during
the execution of triggered SQL statements are returned using SQLSTATE
09000 unless the error is considered severe. If the error is severe,
the severe error SQLSTATE is returned. The SQLERRMC field of the SQLCA
for non-severe error will include the trigger name, SQLCODE, SQLSTATE
and as many tokens as will fit from the tokens of the failure.
The SQL-procedure-statement could
include a SIGNAL SQLSTATE statement or a RAISE_ERROR function. In
both these cases, the SQLSTATE returned is the one specified in the
SIGNAL SQLSTATE statement or the RAISE_ERROR condition.
- Creating a trigger with a schema name that does not already exist
will result in the implicit creation of that schema provided the authorization
ID of the statement has IMPLICIT_SCHEMA authority. The schema owner
is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC.
- DB2SECURITYLABEL
column: A DB2SECURITYLABEL column can be referenced in the
trigger body of a BEFORE TRIGGER but it cannot be changed in the body
of a BEFORE trigger (SQLSTATE 42989).
- BUSINESS_TIME
period columns: The start and end columns of a BUSINESS_TIME
period cannot be changed in the body of BEFORE UPDATE trigger (SQLSTATE
42808).
- Read-only views: 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.
- Transition variable values and INSTEAD OF triggers:
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
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
- Otherwise, the corresponding new transition variable is the existing
value of the column in the row.
- Triggers and typed tables: A BEFORE or AFTER trigger
can be attached to a typed table at any level of a table hierarchy.
If an SQL statement activates multiple triggers, the triggers will
be executed in their creation order, even if they are attached to
different tables in the typed table hierarchy.
When a trigger is
activated, its transition variables (OLD, NEW, OLD TABLE and NEW TABLE)
may contain rows of subtables. However, they will contain only columns
defined on the table to which they are attached.
Effects of
INSERT, UPDATE, and DELETE statements:
- Row triggers: When an SQL statement is used to INSERT, UPDATE,
or DELETE a table row, it activates row-triggers attached to the most
specific table containing the row, and all supertables of that table.
This rule is always true, regardless of how the SQL statement accesses
the table. For example, when issuing an UPDATE EMP command, some of
the updated rows may be in the subtable MGR. For EMP rows, the row-triggers
attached to EMP and its supertables are activated. For MGR rows, the
row-triggers attached to MGR and its supertables are activated.
- Statement triggers: An INSERT, UPDATE, or DELETE statement activates
statement-triggers attached to tables (and their supertables) that
could be affected by the statement. This rule is always true, regardless
of whether any actual rows in these tables were affected. For example,
on an INSERT INTO EMP command, statement-triggers for EMP and its
supertables are activated. As another example, on either an UPDATE
EMP or DELETE EMP command, statement triggers for EMP and its supertables
and subtables are activated, even if no subtable rows were updated
or deleted. Likewise, a UPDATE ONLY (EMP) or DELETE ONLY (EMP) command
will activate statement-triggers for EMP and its supertables, but
not statement-triggers for subtables.
Effects of DROP TABLE statements: A DROP TABLE statement
does not activate any triggers that are attached to the table being
dropped. However, if the dropped table is a subtable, all the rows
of the dropped table are considered to be deleted from its supertables.
Therefore, for a table T:
- Row triggers: DROP TABLE T activates row-type delete-triggers
that are attached to all supertables of T, for each row of T.
- Statement triggers: DROP TABLE T activates statement-type delete-triggers
that are attached to all supertables of T, regardless of whether T
contains any rows.
Actions on Views: To predict what triggers are activated
by an action on a view, use the view definition to translate that
action into an action on base tables. For example:
- An SQL statement performs UPDATE V1, where V1 is a typed view
with a subview V2. Suppose V1 has underlying table T1, and V2 has
underlying table T2. The statement could potentially affect rows in
T1, T2, and their subtables, so statement triggers are activated for
T1 and T2 and all their subtables and supertables.
- An SQL statement performs UPDATE V1, where V1 is a typed view
with a subview V2. Suppose V1 is defined as SELECT ... FROM ONLY(T1)
and V2 is defined as SELECT ... FROM ONLY(T2). Since the statement
cannot affect rows in subtables of T1 and T2, statement triggers are
activated for T1 and T2 and their supertables, but not their subtables.
- An SQL statement performs UPDATE ONLY(V1), where V1 is a typed
view defined as SELECT ... FROM T1. The statement can potentially
affect T1 and its subtables. Therefore, statement triggers are activated
for T1 and all its subtables and supertables.
- An SQL statement performs UPDATE ONLY(V1), where V1 is a typed
view defined as SELECT ... FROM ONLY(T1). In this case, T1 is the
only table that can be affected by the statement, even if V1 has subviews
and T1 has subtables. Therefore, statement triggers are activated
only for T1 and its supertables.
- MERGE statement and triggers: The MERGE statement
can execute update, delete, and insert operations. The applicable
UPDATE, DELETE, or INSERT triggers are activated for the MERGE statement
when an update, delete, or insert operation is executed.
- Obfuscation:
The CREATE TRIGGER statement can be submitted in obfuscated form.
In an obfuscated statement, only the trigger name is readable. The
rest of the statement is encoded in such a way that is not readable
but can be decoded by the database server. Obfuscated statements can
be produced by calling the DBMS_DDL.WRAP function.
- Creating
a trigger with the SECURED option: Normally users with SECADM
authority do not have privileges to create database objects such as
triggers or user-defined functions. Typically, they will examine the
data accessed by a trigger, ensure it is secure, then grant the CREATE_SECURE_OBJECT
authority to someone who has the required privileges to create the
secure trigger. After the trigger is created, they will revoke the
CREATE_SECURE_OBJECT authority from the trigger owner.
The trigger
is considered secure. The database manager treats the SECURED attribute
as an assertion that declares the user has established an audit procedure
for all activities in the trigger body. If a secure trigger references
user-defined functions, the database manager assumes those functions
are secure without validation. If those functions can access sensitive
data, the user with SECADM authority needs to ensure those functions
are allowed to access those data 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
has row level or column level access control activated. Similarly,
a trigger must be secure if its subject table is a view and one or
more of the underlying tables in the view definition has row level
or column level access control activated.
- Creating
a trigger with the NOT SECURED option: The CREATE TRIGGER
statement returns an error if the trigger's subject table has row
level or column level access control activated. Similarly, the CREATE
TRIGGER statement fails if the trigger is defined on a view and one
or more of the underlying tables in that view definition has row level
or column level access control activated.
- Row
and column access control that is not enforced for transition variables
and transition tables: Triggers are used for database integrity,
and as such a balance between security and database integrity is needed.
If row level or column level access control is activated on the subject
table or an underlying table of the subject view, row permissions
and column masks are not applied to the initial values of transition
variables and transition tables. Row level and column level access
control that is enforced for the subject table or an underlying table
of the subject view is also ignored for transition variables and transition
tables that are referenced in the trigger body or are passed as arguments
to user-defined functions invoked in the trigger body. To ensure there
is no security concern for SQL statements in the trigger action to
access sensitive data in transition variables and transition tables,
the trigger must be created with the SECURED option. If a trigger
is not secure, the CREATE TRIGGER statement returns an error.
- Considerations
for implicitly hidden columns: A transition variable exists
for any column defined as implicitly hidden. In the body of a trigger,
a transition variable that corresponds to an implicitly hidden column
can be referenced.
- Rebinding
dependent packages: Every compiled trigger has a dependent
package. The package can be rebound at any time by using the REBIND_ROUTINE_PACKAGE
procedure. Explicitly rebinding the dependent package does not revalidate
an invalid trigger. Revalidate an invalid trigger by using automatic
revalidation or explicitly by using the ADMIN_REVALIDATE_DB_OBJECTS
procedure. Trigger revalidation automatically rebinds the dependent
package.
- Syntax alternatives:
The following syntax alternatives are supported for compatibility with previous versions of DB2® and with other database products. These alternatives are
non-standard and should not be used.
- OLD_TABLE can be specified in place of OLD TABLE, and NEW_TABLE can be specified in place of NEW
TABLE
- MODE DB2SQL can be specified following FOR EACH ROW or FOR EACH STATEMENT
Examples
- Example 1: Create two triggers that will result in the
automatic tracking of the number of employees a company manages. The
triggers will interact with the following tables:
- EMPLOYEE table with these columns: ID, NAME, ADDRESS, and POSITION.
- COMPANY_STATS table with these columns: NBEMP, NBPRODUCT, and
REVENUE.
The first trigger 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:
CREATE TRIGGER NEW_HIRED
AFTER INSERT ON EMPLOYEE
FOR EACH ROW
UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1
The
second trigger decrements the number of employees each time an employee
leaves the company; that is, each time a row is deleted from the table
EMPLOYEE:
CREATE TRIGGER FORMER_EMP
AFTER DELETE ON EMPLOYEE
FOR EACH ROW
UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1
- Example 2: Create a trigger that ensures that whenever
a parts record is updated, the following check and (if necessary)
action is taken:
- If the on-hand quantity is less than 10% of the maximum stocked
quantity, then issue a shipping request ordering the number of items
for the affected part to be equal to the maximum stocked quantity
minus the on-hand quantity.
The trigger will interact with the PARTS table with these
columns: PARTNO, DESCRIPTION, ON_HAND, MAX_STOCKED, and PRICE.
ISSUE_SHIP_REQUEST
is a user-defined function that sends an order form for additional
parts to the appropriate company.
CREATE TRIGGER REORDER
AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
REFERENCING NEW AS N
FOR EACH ROW
WHEN (N.ON_HAND < 0.10 * N.MAX_STOCKED)
BEGIN ATOMIC
VALUES(ISSUE_SHIP_REQUEST(N.MAX_STOCKED - N.ON_HAND, N.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
BEGIN ATOMIC
SELECT ISSUE_SHIP_REQUEST(MAX_STOCKED - ON_HAND, PARTNO)
FROM NTABLE
WHERE (ON_HAND < 0.10 * MAX_STOCKED);
END
- Example 4: Create a trigger that will cause an error when
an update occurs that would result in a salary increase greater than
ten percent of the current salary.
CREATE TRIGGER RAISE_LIMIT
AFTER UPDATE OF SALARY ON EMPLOYEE
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
WHEN (N.SALARY > 1.1 * O.SALARY)
SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Salary increase>10%'
- Example 5: Consider an application which records and tracks
changes to stock prices. The database contains two tables, CURRENTQUOTE
and QUOTEHISTORY.
Tables: CURRENTQUOTE (SYMBOL, QUOTE, STATUS)
QUOTEHISTORY (SYMBOL, QUOTE, QUOTE_TIMESTAMP)
When
the QUOTE column of CURRENTQUOTE is updated, the new quote should
be copied, with a timestamp, to the QUOTEHISTORY table. Also, the
STATUS column of CURRENTQUOTE should be updated to reflect whether
the stock is: - rising in value;
- at a new high for the year;
- dropping in value;
- at a new low for the year;
- steady in value.
CREATE TRIGGER statements that accomplish this are as follows.
- Trigger Definition to set the status:
CREATE TRIGGER STOCK_STATUS
NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE
REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE
FOR EACH ROW
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 Definition to record change in QUOTEHISTORY table:
CREATE TRIGGER RECORD_HISTORY
AFTER UPDATE OF QUOTE ON CURRENTQUOTE
REFERENCING NEW AS NEWQUOTE
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO QUOTEHISTORY
VALUES (NEWQUOTE.SYMBOL, NEWQUOTE.QUOTE, CURRENT TIMESTAMP);
END
- Example 6:Create
a trigger that overrides any changes to the location field in the
employee record in the org table. This trigger would be useful if
new employee records acquired when a smaller company was purchased
are processed and the target location allocated to the employee is
'Toronto' and the new target location is 'Los Angeles'. The before
trigger will ensure that regardless what value the application allocates
for this field, that the final resultant value is 'Los Angeles'.
CREATE TRIGGER LOCATION_TRIGGER
NO CASCADE
BEFORE UPDATE ON ORG
REFERENCING
OLD AS PRE
NEW AS POST
FOR EACH ROW
WHEN (POST.LOCATION = 'Toronto')
SET POST.LOCATION = 'Los Angeles';
END
- Example
7: Create a BEFORE trigger that automatically validates XML documents
containing new product descriptions before they are inserted into
the PRODUCT table of the SAMPLE database:
CREATE TRIGGER NEWPROD NO CASCADE BEFORE INSERT ON PRODUCT
REFERENCING NEW AS N
FOR EACH ROW
BEGIN ATOMIC
SET (N.DESCRIPTION) = XMLVALIDATE(N.DESCRIPTION
ACCORDING TO XMLSCHEMA ID product);
END
- Example
8: Create a multiple-event trigger that tracks of the number and
salary of employees a company manages. The triggers will interact
with the following columns and tables:
- ID, NAME, ADDRESS, SALARY, and POSITION columns in the EMPLOYEE
table
- NBEMP, NBPRODUCT, and REVENUE columns in the COMPANY_STATS table
The trigger increments the number of employees each time a new
employee is hired; decrements the number of employees each time an
employee leaves the company, and raises an error when an update occurs
that would result in a salary increase greater than ten percent of
the current salary: CREATE OR REPLACE TRIGGER HIRED
AFTER INSERT OR DELETE OR UPDATE OF SALARY ON EMPLOYEE
REFERENCING NEW AS N OLD AS O FOR EACH ROW
BEGIN
IF INSERTING THEN UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;
ELSEIF DELETING THEN UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1;
ELSEIF (UPDATING AND (N.SALARY > 1.1 * O.SALARY))
THEN SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Salary increase>10%';
END IF;
END;
- Example
9: Create a trigger that ensures that the following check and
(if necessary) action is taken, before a parts record is updated:
- If the on-hand quantity is less than 10% of the maximum stocked
quantity, then place a new order record into the ORDER table and issue
a shipping request ordering the number of items for the affected part
to be equal to the maximum stocked quantity minus the on-hand quantity.
The trigger interacts with the following columns and tables:- PARTNO, DESCRIPTION, ON_HAND, MAX_STOCKED, and PRICE columns in
the PARTS table
- PARTNO and PRICE columns in the ORDER table
ISSUE_SHIP_REQUEST is a user-defined SQL data modification stored
procedure that sends an order form for additional parts to the supply
company, and deletes the corresponding row from the ORDER table after
the order form is confirmed by the supply company. CREATE TRIGGER REORDER
BEFORE UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
REFERENCING NEW AS N
FOR EACH ROW
WHEN (N.ON_HAND < 0.10 * N.MAX_STOCKED)
BEGIN
INSERT INTO ORDERS VALUES (N.MAX_STOCKED - N.ON_HAND, N.PARTNO);
CALL ISSUE_SHIP_REQUEST(N.MAX_STOCKED - N.ON_HAND, N.PARTNO);
END;