Row and Statement Level Rules

The FOR EACH clause optionally allows defining a row or statement rule; FOR EACH ROW is the default and the only option for BEFORE rules.

When the row level rule is executed, a parameter list is built and the procedure is invoked for each row touched by the statement. If a single DELETE or UPDATE statement affects 100 rows, the procedure invocation occurs 100 times.

When the statement level rule is executed, the parameters passed in the procedure invocation for each qualifying row of the triggering statement are accumulated in an internal temporary table. The temporary table, containing information from all rows touched by the triggering statement, is passed with a single call to the rule procedure. This can potentially save many calls to the rule procedure.

All qualifying rows contained in an internal temporary table are processed by the triggering statement so that the rule procedure is invoked just once.

Examples of both row and statement level rules follow.

In this example, a row level rule (the default) executes the ruleproc1 procedure for every INSERT INTO table_x WHERE col1 > 5000:

create rule r1 after insert into table_x where new.col1> 5000
execute procedure ruleproc1 (p1 = new.col1, p2 = new.col5);

The following example is an exact equivalent of the preceding one; either version can be used:

create rule r1 after insert into table_x where new.col1> 5000
for each row execute procedure ruleproc1 (p1 = new.col1, p2 = new.col5);

In this example, a statement level rule executes the procedure ruleproc2 after each delete of table_y. The col1 and col2 values for each row deleted by a single statement are accumulated in an internal temporary table and are passed together to ruleproc2 with a single call:

create rule r2 after delete from table_y
for each statement execute procedure ruleproc2 (q1 = old.col1, q2 = old.col2);

Database Procedures

Proc_name is the name of the database procedure that is executed whenever the rule fires. The specified procedure must exist when the rule is created. For information about defining database procedures, see Create Procedure.

To execute a database procedure owned by another user, specify schema.procedurename, where schema is the user identifier of the owner of the procedure; you must have execute privilege for the procedure.

The parameter list allows values to be passed to the invoked procedure. The number and type of the parameters must be consistent with the number and type in the definition of the invoked procedure.

The values can include constants, expressions, or references to (old and new) values in the row that caused the rule to fire. (Old and new see values in the row before and after the specified change.) When the value is a constant, the keywords, user and null, are acceptable values. A constant value cannot be a function expression, such as date('now').

Whenever value refers to a value in a row, it must be referenced by a correlation name. The referencing clause allows you to choose these correlation names. For example, the following statement establishes the correlation name, first, for referencing old values and, second, for referencing new values:

create rule r1 after update on table1
referencing old as first new as second
execute procedure p1
        (a = first.c1, b = second.c1);

Old and new correlation names can be specified in any order in the referencing clause. If correlation names are not chosen, the default is as follows:

referencing old as old new as new

If the name of the table is used as the correlation name, the DBMS Server assumes that the values referenced are new values.

If the statement_type in the table condition is insert, only new column values are available for the procedure. If the statement_type is delete, only old column values are available.

If both old and new correlation names are specified in a rule that includes an insert or a delete, or in the statement_type list, the DBMS Server assumes that both the old and new correlation names see the same set of values when the rule fires as a result of an insert or delete.

For example, assume the following rule:

create rule few_parts after update, delete
from parts
        execute procedure check_change
        (name = old.name, pre = old.quantity,
        post = new.quantity)

If an update fires the rule, the values passed to the procedure are the old and new values. However, if a delete fires the rule, the DBMS Server assumes that the correlation names are both old because the new value does not exist in a delete operation.

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|OFF|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);