Databases Reference
In-Depth Information
performs this test. If the employee is indeed paid more than the boss, then
the salary of the boss is increased.
However, that is not the only way in which the constraint can be vio-
lated by salary changes. Any reduction to the salary of a boss could cause the
boss to be paid less than (one or more) of the bosss subordinates. This situa-
tion can be handled by the following rule:
after update of salary on employee
if (new.salary < old.salary and
new.salary < (select max(S.salary)
from employee S
where S.bossname = new.name))
do update employee
set salary = new.salary
where bossname = new.name and salary
> old.salary
That rule implements a less generous policy than some of the earlier ones, in
that it reduces the salary of all better paid subordinates to the salary of the
now not-so-well-paid boss.
The other update operation that must be monitored is a change to the
boss of an employee, which can be addressed by the following rule:
after update of bossname on employee
if new.salary < (select B.salary
from employee B
where B.name = old.bossname)
do rollback
That example illustrates rule support for a recovering business policy.
As is probably evident from these examples, writing active rules is by no
means straightforward. Even in this none-too-complex example, identifying
the events that are able to violate the integrity constraint is not trivial, and
ensuring that appropriate responses are made to similar but different hap-
penings (e.g., salary increases must be responded to differently from salary
decreases) can be quite involved. Overall, active rules can be seen as being
quite powerful, in that a range of tasks can be supported by active rules that
would be much more difficult without them. However, writing correct rules
is a skilled task.
