CREATE TRIGGER 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

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

trigger-event

triggered-action

SQL-procedure-statement

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 the name of a temporary table that identifies the values in the complete set of affected rows prior to the triggering SQL operation. If the trigger event is INSERT, the temporary table is empty.

NEW TABLE AS identifier

Specifies the name of a temporary table that identifies the state of the complete set of affected rows as modified by the triggering SQL operation and by any SET statement in a before trigger that has already been 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).

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 set to DEFERRED_FORCE, 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:

  1. 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.
  2. 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.
  3. 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.
  4. 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:
  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 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;