The CREATE TRIGGER statement defines a trigger. You can create the following types of triggers:
INSERT BEFORE - The triggered-action is executed before any changes are made by an insert on the subject table.
UPDATE BEFORE - The triggered-action is executed before any changes are made by an udpate on the subject table.
DELETE BEFORE - The triggered-action is executed before any changes are made by a delete on the subject table.
INSERT AFTER - The triggered-action is executed after any changes are made by an insert on the subject table.
UPDATE AFTER - The triggered-action is executed after any changes are made by an update on the subject table.
DELETE AFTER - The triggered-action is executed after any changes are made by a delete on the subject table.
Create a trigger, REORDER, that invokes user-defined function ISSUE_SHIP_REQUEST to issue a shipping request whenever a parts record is updated and the on-hand quantity for the affected part is less than 10% of its maximum stocked quantity. User-defined function ISSUE_SHIP_REQUEST orders a quantity of the part that is equal to the part's maximum stocked quantity minus its on-hand quantity; the function also ensures that the request is sent to the appropriate supplier.
The parts records are in the PARTS table. Although the table has more columns, the trigger is activated only when columns ON_HAND or MAX_STOCKED are updated.
CREATE TRIGGER REORDER AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS REFERENCING NEW AS NROW FOR EACH ROW MODE DB2SQL WHEN (NROW.ON_HAND < 0.10 * NROW.MAX_STOCKED) BEGIN ATOMIC VALUES (ISSUE_SHIP_REQUEST(NROW.MAX_STOCKED - NROW.ON_HAND, NROW.PARTNO)); END
Repeat the scenario except use a fullselect instead of a VALUES statement to invoke the user-defined function. This example also shows how to define the trigger as a statement trigger instead of a row trigger. For each row in the transition table that evaluates to true for the WHERE clause, a shipping request is issued for the part.
CREATE TRIGGER REORDER AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS REFERENCING NEW TABLE AS NTABLE FOR EACH STATEMENT MODE DB2SQL BEGIN ATOMIC SELECT ISSUE_SHIP_REQUEST(MAX_STOCKED - ON_HAND, PARTNO) FROM NTABLE WHERE ON_HAND < 0.10 * MAX_STOCKED; END
You can also use the VALUES statement to invoking a user-defined function from a trigger. Transition variables can be passed to the user-defined function.
Create an after trigger EMPISRT1 that invokes
user-defined function NEWEMP when the trigger is activated. An
insert
operation on table EMP activates the trigger. Pass transition variables for the
new employee number, last
name, and first name to the user-defined function.
CREATE TRIGGER EMPISRT1 AFTER INSERT ON EMPLOYEE REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC VALUES ( NEWEMP(N.EMPNO, N.LASTNAME, N.FIRSTNAME)); END
For more information, see CREATE TRIGGER and VALUES in the SQL Reference topic in the
Information Center.