Procedure Execution in Create Rule

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. Use Create Procedure (see CREATE PROCEDURE) to define a database 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:

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.