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

Trigger activation time:

The choices for trigger activation time are BEFORE, AFTER, and INSTEAD OF. BEFORE and AFTER triggers can be defined for a table. INSTEAD OF triggers can be defined for a view.

BEFORE means that the trigger is activated before Db2 makes any changes to the subject table, and that the triggered action does not activate any other triggers. AFTER means that the trigger is activated after Db2 makes changes to the subject table and can activate other triggers. INSTEAD OF means that the trigger is activated when there is an attempt to change the subject view. Triggers with an activation time of BEFORE are known as before triggers. Triggers with an activation time of AFTER are known as after triggers. Triggers with an activation time of INSTEAD OF are known as instead of triggers.

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 delete, 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
  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
  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
  BEGIN ATOMIC
    UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;
  END

Transition variables:

When you code a row trigger, you might need to refer to the values of columns in each updated row of the subject table or view. To do this, specify a correlation name (to use when referencing transition variables) in the REFERENCING clause of your CREATE TRIGGER statement. The two types of transition variables are:

• Old transition variables capture the values of columns before the triggering SQL statement updates them. You can use the REFERENCING OLD clause to define a correlation name for referencing old transition variables for update and delete triggers.
• New transition variables capture the values of columns after the triggering SQL statement updates them. You can use the REFERENCING NEW clause to define a correlation name for referencing new transition variables for update and insert triggers.

Transition variables can be referenced anywhere in an SQL statement where an expression or variable can be specified in triggers. See References to SQL parameters and variables in SQL PL for more information.

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:

If you want to refer to the entire set of rows that a triggering SQL statement modifies, rather than to individual rows, use a transition table. Like transition variables, a correlation name (to refer to the columns of the transition table) can appear in the REFERENCING clause of a CREATE TRIGGER statement. The names for those columns are the same as the name of the column in the table or view that the trigger is defined for. Transition tables are valid for both row triggers and statement triggers. The two types of transition tables are:

• Old transition tables, specified with the OLD TABLE transition-table-name clause, capture the values of columns before the triggering SQL statement updates them. You can define old transition tables for update and delete triggers.
• New transition tables, specified with the NEW TABLE transition-table-name clause, capture the values of columns after the triggering SQL statement updates them. You can define new transition variables for update and insert triggers.

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. The name of a transition variable can also be the same as the name of an SQL variable or global variable, or the name of a column in a table or view that is referenced in the trigger. Names that are the same should be explicitly qualified. Qualifying a name can clarify whether the name refers to a column, global variable, SQL variable, SQL parameter, or transition variable. To avoid ambiguity, qualify a transition variable with the correlation name specified in the REFERENCING clause in the CREATE TRIGGER or ALTER TRIGGER statement that defined the trigger.

CREATE TRIGGER LRG_ORDR
  AFTER INSERT ON INVOICE
  REFERENCING NEW TABLE AS N_TABLE
  FOR EACH STATEMENT
  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
  BEFORE INSERT ON ORDERS
  REFERENCING NEW AS NEW_ORDER
  FOR EACH ROW
  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. The trigger body can include a single SQL-control-statement, including a compound statement, or triggered-SQL-statement that is to be executed for the triggered-action. The statements that you can use in a trigger body depend on the activation time of the trigger. See CREATE TRIGGER (advanced) and SQL procedural language (SQL PL) for more information about defining SQL triggers. Use control statements to develop triggers that contain logic.

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