Multiple event SQL triggers
A single trigger can be defined for more than one insert, update, or delete event.
A trigger can be defined to handle one, two, or all three of the insert, update, and delete events. The only restriction is that the events must share a common BEFORE, AFTER, or INSTEAD OF triggering time. Trigger event predicates can be used within the trigger body to distinguish which event caused the trigger to be activated.
In the following example, several warning SQLSTATEs are grouped into a single trigger definition.
CREATE TABLE PARTS (INV_NUM INT, PART_NAME CHAR(20), ON_HAND INT, MAX_INV INT,
PRIMARY KEY (INV_NUM))
CREATE OR REPLACE TRIGGER INVENTORY_WARNINGS
AFTER DELETE OR INSERT OR UPDATE OF ON_HAND ON PARTS
REFERENCING NEW AS N_INV
OLD AS O_INV
FOR EACH ROW MODE DB2SQL
BEGIN
IF INSERTING THEN
IF (N_INV.ON_HAND > N_INV.MAX_INV) THEN
BEGIN
SIGNAL SQLSTATE '75001' ('Inventory on hand exceeds maximum allowed.');
END;
END IF;
ELSEIF UPDATING THEN
IF (N_INV.ON_HAND < 5) THEN
BEGIN
SIGNAL SQLSTATE '75002' ('Inventory low - Re-order soon.');
END;
END IF;
ELSEIF DELETING THEN
IF (O_INV.ON_HAND > 0) THEN
BEGIN
SIGNAL SQLSTATE '75004' ('Deleting a part while inventory still in stock.');
END;
END IF;
END IF;
END
In the trigger body the INSERTING, UPDATING, and DELETING
predicates are used to determine which event caused the trigger to
activate. A distinct piece of code is executed for each of the defined
events. For each event, a warning condition is handled.
In the next example, the trigger event predicates are used
in an INSERT statement to generate a row for a transaction history
table.
CREATE TABLE TRANSACTION_HISTORY(INV_NUM INT, POSTTIME TIMESTAMP,
TRANSACTION_TYPE CHAR(1))
CREATE TRIGGER SET_TRANS_HIST
AFTER INSERT OR UPDATE OR DELETE ON PARTS
REFERENCING NEW AS N
OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN
INSERT INTO TRANSACTION_HISTORY VALUES (
CASE
WHEN INSERTING OR UPDATING
THEN N.INV_NUM
WHEN DELETING
THEN O.INV_NUM
END,
CURRENT TIMESTAMP,
CASE
WHEN INSERTING
THEN 'I'
WHEN UPDATING
THEN 'U'
WHEN DELETING
THEN 'D'
END
);
END
For this trigger, the same routine logic is used by all three
trigger events. The trigger event predicates are used to determine
whether the inventory number needs to be read from the before or after
row value and a second time to set the type of transaction.