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);
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 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:
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.