Table_Condition
The table_condition on a CREATE RULE statement defines the action that fires the rule.
The table_condition has the following format:
BEFORE|AFTER statement_type {, statement_type} ON|OF|FROM|INTO [schema.]table_name
[REFERENCING [OLD AS old_corr_name] [NEW AS new_corr_name]]
[WHERE qualification]
BEFORE|AFTER
Specifies that the rule be fired before or after the effective execution of the triggering statement.
statement_type
Specifies the type of statement that fires (triggers) the rule. Valid values include:
INSERT
UPDATE[(column_name {, column_name})]
DELETE
Note: Only one of each statement type can be included in a single table_condition.
table_name
Specifies the table against which the rule is created.
old_corr_name
Specifies the correlation name for old (prior to change) values in a row. The name specified is used to qualify references to old values in the parameter list and the qualification.
Default: Old
new_corr_name
Specifies the correlation name for new (after the change) values in a row. The name specified is used to qualify new values in the parameter list and qualification.
Default: New
qualification
Indicates the specific change that must occur to the table to fire the rule. The qualification must evaluate to true or false.
Any column references in qualification must be qualified by the correlation names defined in the referencing clause or by the default correlation names, old and new.
The qualification cannot include a subselect or an aggregate (set) function such as count or sum.
The action that fires the rule can be:
• An INSERT, UPDATE, or DELETE performed on any column in the table.
For example, the following rule fires whenever an INSERT is executed against the employee table:
create rule insert_emp after insert into employee
execute procedure new_emp (name = new.name,
addr = new.address);
In the above example, the AFTER keyword dictates that the rule fires after the INSERT statement is executed. This can be used, for example, to store auditing information in another table.
If the rule uses the BEFORE keyword, it fires before the INSERT is executed. This can be used to alter the value of the name or addr columns (for example, after validating them against another table).
Note: If a column name is not specified after update, the rule fires after an update to any column in the table.
• An update performed on specified columns in a table.
For example, the following rule fires whenever the salary column in the employee table is changed.
create rule emp_salary after update(salary)
of employee
execute procedure check_sal
(name = old.name, oldsal = old.salary,
newsal = new.salary);
Up to 1024 columns can be specified in the UPDATE clause. The rule is fired whenever one or more of the columns is updated.
• A change to the table that satisfies the specified WHERE clause qualification.
For example, the following rule fires whenever an update to the quantity column of the parts table results in a quantity value of less than 50:
create rule few_parts after update(quantity)
of parts
where new.quantity < 50
execute procedure issue_order
(name = old.name,
quantity = new.quantity);