Posted by: Serge Rielau
You may know that I lead the team enhancing DB2 to simplify Oracle application conversion to DB2.
A fair number of features we have added, such as VARCHAR2 are not terribly interesting for true blue customers.
But there are also quit a few features which add value for everyone.
One such feature commonly called multi-action trigger support was added in DB2 9.7.4 released in April 2011.
A triggers, as I''m sure you know, consists of procedural logic specified in SQL PL (or PL/SQL) which is executed when the trigger subject table is modified.
You can classify triggers along three dimensions:
- Trigger granularity
The trigger can fire either
- FOR EACH ROW which is modified on the subject table or
- exactly once FOR EACH STATEMENT that modifies the subject table.
- Trigger time
A trigger can either execute
- BEFORE the modification actually happens. This time allows the trigger to modify row content upfront.
For example BEFORE triggers are used to check complex constraints on the row or fill in defaults based on other columns in the row or other tables.
- AFTER the modification has happened.
This kind of trigger is most often used to execute cascading events such as propagating the change to other tables or sending email notifications.
- INSTEAD OF the modification specified.
Here the trigger actually replaces the data change statement itself.
The most common usage of an instead of trigger is to translate a modification to a view that is not normally updatable into the desired semantic.
For example an INSTEAD OF trigger on a view consisting of a join of two tables might cause individual updates to the two component tables.
INSTEAD OF triggers are only supported for views today.
- Trigger event
The trigger event specifies the kind of modification on which the trigger is to fire
- DELETE triggers fire whenever a DELETE statement or a MERGE statement with a DELETE component is executed
- UPDATE triggers fire whenever an UPDATE statement or a MERGE statement with an UPDATE component is executed.
- INSERT triggers fire, I'm sure you''re getting the picture, whenever an INSERT statement or a MERGE statement with an INSERT component is executed.
Now, let's define a simple scenario with a table and a couple of triggers.
CREATE TABLE emp(name VARCHAR(10), rank INTEGER, location VARCHAR(10), salary INTEGER);
Some locations have higher living expenses than others, causing salaries to vary based not only on the position of the employee, but also on the location.
This complex dependency cannot be modeled with a regular DEFAULT clause.
So you may either implement a HIRE procedure to implement the derivation of starting salaries in absence of a value provided by the human resources department or, if you like, use a trigger like this one:
CREATE OR REPLACE TRIGGER empBeforeInsert BEFORE INSERT ON emp
REFERENCING NEW AS n FOR EACH ROW
WHEN (n.salary IS NULL)
SET n.salary = CASE n.rank WHEN 1 THEN 10000
WHEN 2 THEN 15000
WHEN 3 THEN 20000
WHEN 4 THEN 25000
ELSE 30000 END
* CASE n. location WHEN 'Boston' THEN 1.2
WHEN 'New York' THEN 2
WHEN 'Moncton' THEN 0.8
ELSE 1 END;
Let's do some hiring:
INSERT INTO emp VALUES
('Jill', 2, 'Boston' , DEFAULT),
('Jack', 4, 'New York', DEFAULT),
('John', 3, 'Toronto' , DEFAULT),
('Jason', 5, 'Moncton' , 50000);
SELECT * FROM emp ORDER BY name;
NAME RANK LOCATION SALARY
---------- ----------- ---------- -----------
Jack 4 New York 50000
Jason 5 Moncton 50000
Jill 2 Boston 18000
John 3 Toronto 20000
OK, the trigger seems to work.
We do have a problem though. Some of the employees hired into New York have figured out that live is less expensive in Moncton and want to move.
Not only that. You have some employees that you would like to move from Toronto to Boston and they are not happy when they see the housing prices.
So we need another trigger to accommodate location changes without having to negotiate with each employee individually.
CREATE OR REPLACE TRIGGER empBeforeUpdate BEFORE UPDATE OF location ON emp
REFERENCING OLD AS o NEW AS n FOR EACH ROW
WHEN (n.salary IS NULL OR n.salary = o.salary)
SET n.salary = DECFLOAT(o.salary)
/ CASE o.location WHEN 'Boston' THEN 1.2
WHEN 'New York' THEN 2
WHEN 'Moncton' THEN 0.8
ELSE 1 END
* CASE n.location WHEN 'Boston' THEN 1.2
WHEN 'New York' THEN 2
WHEN 'Moncton' THEN 0.8
ELSE 1 END;
We can now relocate our employees:
UPDATE emp SET location = 'Boston' WHERE name = 'Jason';
UPDATE emp SET location = 'Toronto' WHERE name = 'Jill';
UPDATE emp SET location = 'New York',
salary = 63000 WHERE name = 'John';
SELECT * FROM emp ORDER BY name;
NAME RANK LOCATION SALARY
---------- ----------- ---------- -----------
Jack 4 New York 50000
Jason 5 Boston 75000
Jill 2 Toronto 15000
John 3 New York 63000
This worked as well.
Now, the only problem the above approach has is that our logic is spread across multiple objects. Every time we re-evaluate the living costs in different cities we need to remember to update both triggers. This is error prone.
Certainly what we could do in this case is to store the modifiers in a table and perhaps use a function to encapsulate much of the logic, but then, this is a simple example.
Wouldn't it be nice if we could put both triggers together so we can merge the logic? That is where multi-action triggers come in.
In DB2 9.7.4 you can associate more than one trigger event with any given trigger.
DB2 also provides a set of predefined BOOLEAN variables which you can use to inquire which trigger-event caused the trigger to fire, so you can guide the logic appropriately.
The variables are:
- UPDATING
- DELETING
- INSERTING
The meaning is self explanatory I presume.
Here is one way to merge the two trigger above together:
DROP TRIGGER empBeforeInsert;
DROP TRIGGER empBeforeUpdate;
--#SET TERMINATOR @
CREATE OR REPLACE TRIGGER empBefore BEFORE UPDATE OF location OR INSERT ON emp
REFERENCING NEW AS n OLD AS o FOR EACH ROW
WHEN (n.salary IS NULL OR n.salary = o.salary)
BEGIN
IF UPDATING THEN
SET n.salary = DECFLOAT(o.salary)
/ CASE o.location WHEN 'Boston' THEN 1.2
WHEN 'New York' THEN 2
WHEN 'Moncton' THEN 0.8
ELSE 1 END;
ELSEIF INSERTING THEN
SET n.salary = CASE n.rank WHEN 1 THEN 10000
WHEN 2 THEN 15000
WHEN 3 THEN 20000
WHEN 4 THEN 25000
ELSE 30000 END;
END IF;
SET n.salary = n.salary
* CASE n.location WHEN 'Boston' THEN 1.2
WHEN 'New York' THEN 2
WHEN 'Moncton' THEN 0.8
ELSE 1 END;
END
@
--#SET TERMINATOR ;
This is much cleaner and we could still isolate the CASE expression into a function.
Note that as a consequence of multi-action trigger support OLD and NEW transition variables are supported for all trigger-events.
This includes NEW for DELETE events and OLD for UPDATE events.
DB2 simply provides NULL values for all fields in the inapplicable transition variable.
So o.location and o.salary are both NULL for an INSERT statement.
All that is left now is to test the trigger lives up to its promise. For validation we replay we have done so far:
DELETE FROM emp;
INSERT INTO emp VALUES
('Jill' , 2, 'Boston' , DEFAULT),
('Jack' , 4, 'New York', DEFAULT),
('John' , 3, 'Toronto' , DEFAULT),
('Jason', 5, 'Moncton' , 50000);
SELECT * FROM emp ORDER BY name;
NAME RANK LOCATION SALARY
---------- ----------- ---------- -----------
Jack 4 New York 50000
Jason 5 Moncton 50000
Jill 2 Boston 18000
John 3 Toronto 20000
So far so good. Now for the re-locations:
UPDATE emp SET location = 'Boston' WHERE name = 'Jason';
UPDATE emp SET location = 'Toronto' WHERE name = 'Jill';
UPDATE emp SET location = 'New York',
salary = 63000 WHERE name = 'John';
SELECT * FROM emp ORDER BY name;
NAME RANK LOCATION SALARY
---------- ----------- ---------- -----------
Jack 4 New York 50000
Jason 5 Boston 75000
Jill 2 Toronto 15000
John 3 New York 63000
Beautiful!
As closing remark I want to point out a restriction. The first two triggers were both inlined. That is they used inline SQL PL because there was no BEGIN keyword.
The multi-action trigger support is limited to compiled SQL PL. That is you must use the BEGIN keyword without the ATOMIC clause.