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);
Last modified date: 04/26/2024