Example: Use a General Purpose Rule to Track Changes to Personnel Numbers
This example tracks changes to personnel numbers. When an employee is removed, an entry is made into the manager table, which in turn causes an entry to be made into the director table. Even if an entry is made directly into the manager table, the director table is notified.
To implement this, two database procedures need to be defined. The first, manager_emp_track, updates the manager table by reducing the number of employees for a manager, and inserts an entry into a separate table, mgrlog, to log which employee was deleted for the manager:
Parameters
ename varchar(30),
mname varchar(30)
Statements
update manager set employees = employees – 1
where name = :mname;
insert into mgrlog values ('Manager: ' +
:mname + ', Deleted employee: ' + :ename);
The second, director_emp_track, updates the director table by reducing the number of employees for a director:
Parameters
dname varchar(30)
Statements
update director set employees = employees - 1
where name = :dname;
Two rules also need to be defined. The first one, defined for the employee table, executes manager_emp_track after a delete operation, passing the following parameters:
ename = old.name, mname = old.manager
The second rule, defined for the manager table, executes director_emp_track after an update operation on the employees’ column that reduces the number of employees by one. To implement the rule, the following WHERE clause must be defined:
old.employees - 1 = new.employees
Director_emp_track must be defined as the database procedure with the following parameters:
dname = old.director
This rule is fired by the manager_emp_track procedure, because it reduces the number of employees by one, but it is also fired if the manager table is updated directly.