In the code for a triggered action, you can reference the rows that a trigger event acts on. You do this by treating the rows as a temporary table, naming the table, and then referring to the table by name in the code.
Example:The rows of table EMPLOYEE are periodically updated with the new salaries of employees who have received raises. If, after any such update, the total increment in salaries exceeds 5 percent, you need to notify management.
You can code a triggered action to generate the notification. In the code, you use temporary tables to refer to the updated rows as they are at two points in time--before the update and after the update. The temporary table for the rows before the update is called OLDTABLE, and the temporary table for the rows after the update is called NEWTABLE.
You code the triggered action in a trigger that you call OVERALL_RAISE. Here is the definition of OVERALL_RAISE:
CREATE TRIGGER overall_raise
AFTER UPDATE OF salary ON employee
REFERENCING OLD_TABLE AS oldtable NEW_TABLE AS newtable
FOR EACH STATEMENT MODE DB2SQL
WHEN ((SELECT SUM(salary) FROM newtable) * 100.0000 /
(SELECT SUM(salary) FROM oldtable) > 105)
SIGNAL SQLSTATE '75001' ('Overall salary increase > 5%')
[ Top of Page | Table of Contents | Glossary | Index ]