Creating a trigger

Using triggers for active data:

For example, a constraint can disallow an update to the salary column of the employee table if the new value is over a certain amount. A trigger can monitor the amount by which the salary changes, as well as the salary value. If the change is above a certain amount, the trigger might substitute a valid value and call a user-defined function to send a notice to an administrator about the invalid update.

Triggers also move application logic into DB2, which can result in faster application development and easier maintenance. For example, you can write applications to control salary changes in the employee table, but each application program that changes the salary column must include logic to check those changes. A better method is to define a trigger that controls changes to the salary column. Then DB2 does the checking for any application that modifies salaries.

Example of creating and using a trigger:

Triggers automatically execute a set of SQL statements whenever a specified event occurs. These SQL statements can perform tasks such as validation and editing of table changes, reading and modifying tables, or invoking functions or stored procedures that perform operations both inside and outside DB2.

You create triggers using the CREATE TRIGGER statement. The following figure shows an example of a CREATE TRIGGER statement.

                 1 
CREATE TRIGGER REORDER
    2      3                                4 
  AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
                      5 
  REFERENCING NEW AS N_ROW
   6 
  FOR EACH ROW MODE DB2SQL
   7 
  WHEN (N_ROW.ON_HAND < 0.10 * N_ROW.MAX_STOCKED)
   8 
  BEGIN ATOMIC
  CALL ISSUE_SHIP_REQUEST(N_ROW.MAX_STOCKED -
                                N_ROW.ON_HAND,
                                N_ROW.PARTNO);
  END

When you execute this CREATE TRIGGER statement, DB2 creates a trigger package called REORDER and associates the trigger package with table PARTS. DB2 records the timestamp when it creates the trigger. If you define other triggers on the PARTS table, DB2 uses this timestamp to determine which trigger to activate first when the triggering event occurs. The trigger is now ready to use.

After DB2 updates columns ON_HAND or MAX_STOCKED in any row of table PARTS, trigger REORDER is activated. The trigger calls a stored procedure called ISSUE_SHIP_REQUEST if, after a row is updated, the quantity of parts on hand is less than 10% of the maximum quantity stocked. In the trigger condition, the qualifier N_ROW represents a value in a modified row after the triggering event.

DROP TRIGGER REORDER;

Executing this statement drops trigger REORDER and its associated trigger package named REORDER.

If you drop table PARTS, DB2 also drops trigger REORDER and its trigger package.

Parts of a trigger:

Subject table or view:

When you perform an insert, update, or delete operation on this table or view, the trigger is activated. You must name a local table or view in the CREATE TRIGGER statement. You cannot define a trigger on a catalog table.

Triggering event:

A triggering event can also be an update or delete operation that occurs as the result of a referential constraint with ON DELETE SET NULL or ON DELETE CASCADE.

A trigger can be activated by a MERGE statement for insert and update operations.

Triggers are not activated as the result of updates made to tables by DB2 utilities, with the exception of the LOAD utility when it is specified with the RESUME YES and SHRLEVEL CHANGE options.

When the triggering event for a trigger is an update operation, the trigger is called an update trigger. Similarly, triggers for insert operations are called insert triggers, and triggers for delete operations are called delete triggers.

The SQL statement that performs the triggering SQL operation is called the triggering SQL statement. Each triggering event is associated with one subject table or view and one SQL operation.

CREATE TRIGGER NEW_HIRE
  AFTER INSERT ON EMP
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;
  END

If the triggering SQL operation is an update operation, the event can be associated with specific columns of the subject table. In this case, the trigger is activated only if the update operation updates any of the specified columns.

CREATE TRIGGER PAYROLL1
  AFTER UPDATE OF SALARY, BONUS ON PAYROLL
  FOR EACH STATEMENT MODE DB2SQL
  BEGIN ATOMIC
    VALUES(PAYROLL_LOG(USER, 'UPDATE', CURRENT TIME, CURRENT DATE));
  END

Granularity:

Triggers with a granularity of FOR EACH ROW are known as row triggers. Triggers with a granularity of FOR EACH STATEMENT are known as statement triggers. Statement triggers can only be after triggers.

CREATE TRIGGER NEW_HIRE
  AFTER INSERT ON EMP
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;
  END

Transition variables:

Transition variables can be referenced anywhere in an SQL statement where an expression or variable can be specified in triggers.

The following example uses transition variables and invocations of the IDENTITY_VAL_LOCAL function to access values that are assigned to identity columns.

CREATE TABLE T 
  (ID SMALLINT GENERATED BY DEFAULT AS IDENTITY (START WITH 100), 
   C2 SMALLINT, 
   C3 SMALLINT, 
   C4 SMALLINT);

CREATE TABLE S 
  (ID SMALLINT GENERATED ALWAYS AS IDENTITY,
   C1 SMALLINT);

CREATE TRIGGER TR1
  NO CASCADE BEFORE INSERT
  ON T REFERENCING NEW AS N
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    SET N.C3 =N.ID;
    SET N.C4 =IDENTITY_VAL_LOCAL();
    SET N.ID =N.C2 *10;
    SET N.C2 =IDENTITY_VAL_LOCAL();
  END

INSERT INTO S (C1) VALUES (5);

INSERT INTO T (C2)
  VALUES (IDENTITY_VAL_LOCAL());

Transition tables:

The scope of old and new transition table names is the trigger body. If correlation names are specified for both old and new transition variables in the trigger, a reference to a transition variable must be qualified with the associated correlation name.

CREATE TRIGGER LRG_ORDR
  AFTER INSERT ON INVOICE
  REFERENCING NEW TABLE AS N_TABLE
  FOR EACH STATEMENT MODE DB2SQL
  BEGIN ATOMIC
    SELECT LARGE_ORDER_ALERT(CUST_NO,
    TOTAL_PRICE, DELIVERY_DATE)
    FROM N_TABLE WHERE TOTAL_PRICE > 10000;
  END

The SELECT statement in LRG_ORDER causes user-defined function LARGE_ORDER_ALERT to execute for each row in transition table N_TABLE that satisfies the WHERE clause (TOTAL_PRICE > 10000).

Triggered action:

When a trigger is activated, a triggered action occurs. Every trigger has one triggered action, which consists of an optional trigger condition and a trigger body.

Trigger condition:

If you want the triggered action to occur only when certain conditions are true, code a trigger condition. A trigger condition is similar to a predicate in a SELECT, except that the trigger condition begins with WHEN, rather than WHERE. If you do not include a trigger condition in your triggered action, the trigger body executes every time the trigger is activated.

For a row trigger, DB2 evaluates the trigger condition once for each modified row of the subject table. For a statement trigger, DB2 evaluates the trigger condition once for each execution of the triggering SQL statement.

If the trigger condition of a before trigger has a fullselect, the fullselect cannot reference the subject table.

CREATE TRIGGER CK_AVAIL
  NO CASCADE BEFORE INSERT ON ORDERS
  REFERENCING NEW AS NEW_ORDER
  FOR EACH ROW MODE DB2SQL
  WHEN (NEW_ORDER.QUANTITY >
    (SELECT ON_HAND FROM PARTS
     WHERE NEW_ORDER.PARTNO=PARTS.PARTNO))
     BEGIN ATOMIC
       VALUES(ORDER_ERROR(NEW_ORDER.PARTNO,
         NEW_ORDER.QUANTITY));
     END

Trigger body:

In the trigger body, you code the SQL statements that you want to execute whenever the trigger condition is true. If the trigger body consists of more than one statement, it must begin with BEGIN ATOMIC and end with END. You cannot include host variables or parameter markers in your trigger body.

The statements that you can use in a trigger body depend on the activation time of the trigger. For a list of valid SQL statements for triggers, see Table 2. Allowable SQL statements under SQL-trigger-body in the CREATE TRIGGER topic.

Because you can include INSERT, DELETE (searched), UPDATE (searched), and MERGE statements in your trigger body, execution of the trigger body might cause activation of other triggers. See Trigger cascading for more information.

Examples

   CREATE TRIGGER NEW_HIRE
      AFTER INSERT ON EMPLOYEE
      FOR EACH ROW MODE DB2SQL
      BEGIN ATOMIC
         1 
        UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;
      END

The following list provides more detailed information about SQL statements that are valid in triggers:

• fullselect, CALL, and VALUES

Use a fullselect or the VALUES statement in a trigger body to conditionally or unconditionally invoke a user-defined function. Use the CALL statement to invoke a stored procedure. See Invoking a stored procedure or user-defined function from a trigger for more information on invoking user-defined functions and stored procedures from triggers.

A fullselect in the trigger body of a before trigger cannot reference the subject table.

CREATE TRIGGER SAL_ADJ
  BEFORE UPDATE OF SALARY ON EMP
  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

CREATE TRIGGER HIREDATE
  NO CASCADE BEFORE INSERT ON EMP
  REFERENCING NEW AS NEW_VAR
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    SET NEW_VAR.HIRE_DATE = CURRENT_DATE;
  END