Examples: Create Rule

The following are Create Rule statement examples:

1. The following two rules are applied to the employee table. The first rule fires whenever a change is made to an employee record, to log the action. The second rule fires only when a salary is increased. An update statement that increases the salary fires both the rules-in this case, the order of firing is not defined.

   create rule emp_updates after delete, insert,
       update of employee
       execute procedure track_emp_updates
       (name = new.name);
   
   create rule emp_salary after update(salary, bonus)
       of employee
       where new.salary > old.salary
       execute procedure check_sal
           (name = old.name,
           oldsal = old.salary,
           newsal = new.salary,
       oldbonus = old.bonus,
       newbonus = new.bonus);

2. The following two rules track 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 still notified.

   create procedure manager_emp_track

       (ename varchar(30), mname varchar(30)) as

   begin

       update manager set employees = employees - 1

       where name = :mname;

       insert into mgrlog values

       ('Manager: ' + :mname +

           '. Deleted employee: ' + :ename);

   end;

   create rule emp_delete after delete from employee

       execute procedure manager_emp_track

       (ename = old.name, mname = old.manager);

   create procedure director_emp_track

       (dname varchar(30)) as

   begin

       update director set employees = employees - 1

       where name = :dname;

   end;

   create rule manager_emp_delete

       after update(employees) of manager

       where old.employees - 1 = new.employees

       execute procedure director_emp_track

       (dname = old.director);

3. The following example shows a rule that is fired before the insertion of rows into the customer table. The triggered procedure can verify the zipcode (or other columns) of the inserted row, possibly changing it if the originally-assigned value is in error.

   create rule cust_zip_replace

       before insert into customer

       execute procedure verify_zip

       (custno = c_no, zipcode = c_zip);