All in one...multi-action triggers in DB2 9.7.4
Comments (2) Visits (11977)
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:
A trigger can either execute
The trigger event specifies the kind of modification on which the trigger is to fire
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)
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);
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)
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',
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:
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)
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;
So far so good. Now for the re-locations:
UPDATE emp SET location = 'Boston' WHERE name = 'Jason';
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.