Enforcing General-Purpose Rules
General-purpose rules are those rules that do not fall in the category of either referential or general integrity constraints.
Using a Rule to Apply External Resource Controls
You can use general purpose rules to apply external resource controls.
For example, if you have a table of items in stock, define a rule that fires after an update to the in_stock column. The following WHERE clause causes the rule to fire if the number of items in stock is reduced to less than a minimum value of 100:
items.in_stock < 100
The rule executes a database procedure that reorders the item responsible for firing the rule, passing as parameters an item identifier and the number of items in stock. For example:
id = items.id, items_left = items.in_stock
Using a Rule to Extend the Permission System
A rule can be created to extend the permission system by ensuring that unauthorized users cannot modify certain classified rows in the opcodes table. The rule, which must be fired after inserts and deletes, is defined with the following WHERE clause:
opcodes.scope = 'share' and user != 'system'
The database procedure invoked by this rule can issue an error (using the RAISE ERROR statement, which rejects the statement that fired the rule) and log the operation with the user name into a local log table for later review (the next example demonstrates logging).
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.
Last modified date: 01/30/2023