8. SQL Statements : CREATE RULE : CREATE RULE Examples
 
Share this page                  
CREATE RULE 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 zip code (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);