If a triggered action needs to read or manipulate a value, you can reference this value with a variable in the form of R.C, where R and C identify the row and column where the value is located:
A variable that references a value in the SQL for a triggered action is called a transition variable. The row name in such a variable is called a correlation name.
Example: You want a triggered action to invoke an error message if a dollar amount in the SALARY column of table EMPLOYEE is updated to a figure that's over 110% of the amount. The action needs to reference two values: the original dollar amount and the figure to which it will be updated.
To reference the first value, you can specify a variable called OLDROW.SALARY, where OLDROW refers to the row that contains the value.
To reference the second value, you can specify a variable called NEWROW.SALARY, where NEWROW refers to the row that contains the value.
OLDROW and NEWROW are names for the same row. The name OLDROW signifies that the row contains the original amount; the name NEWROW signifies that the row contains an updated value.
You code the triggered action in a trigger that you call RAISE_LIMIT. Here is the definition of RAISE_LIMIT:
CREATE TRIGGER raise_limit
AFTER UPDATE OF salary ON employee
REFERENCING OLD AS oldrow NEW AS newrow
FOR EACH ROW MODE DB2SQL
WHEN (newrow.salary > 1.1 * oldrow.salary)
SIGNAL SQLSTATE '75000' ('Salary increase > 10%')
For more examples of SQL containing correlation names, see "CREATE TRIGGER" in the SQL Reference.
[ Top of Page | Table of Contents | Glossary | Index ]