How Rules Are Used
After a rule object is created, the rule is stored with the table in the database and is applied continuously. Whenever the execution of a statement satisfies an existing rule condition, that rule is fired, meaning that the database procedure associated with the rule is executed. There is no need for application code to explicitly enforce the rule.
It is also possible for a statement in a rule-invoked database procedure to fire another rule. Rules can be nested in this manner up to a maximum level specified by the DBMS configuration parameter, rule_depth.
Any user who has the privilege to access the table through the operation specified by the rule has implicit permission to fire the rule and execute its associated database procedure. For information on privileges and how they are defined, see the Security Guide.
Before and After Rules
Rules can be defined to execute before or after the effect of the triggering statement is applied. AFTER rules are more common and are used to perform auditing operations, integrity checks, and other operations on the updated rows. BEFORE rules can be used to validate and replace values in an inserted or updated row before the row is stored in the database. Both types of rules can be used to inhibit the execution of the triggering statement if an error condition is encountered, although BEFORE rules can typically do so more efficiently.
Example: Use a Rule to Implement the Equivalent of an Integrity
In this example, you want to create a rule that is the same as an integrity in which the condition is salary <= 50000.
In VDBA, fill in the Create Rule dialog as follows:
1. For Rule Name, enter check_salary.
2. For After, enable Insert and Update. This way, the rule is fired when new rows are added and when existing rows are updated.
3. For Specify Columns for Update, enable salary, because that is the only column you need to check after an update.For Where, enter new.salary > 50000.
Here, “new” is a correlation name, and “new.salary” is a correlation reference. With any column name, you can specify whether you want to use its value before or after the update using the correlation name “old” or “new,” respectively.
Note: Unlike an integrity check, which specifies a condition that cannot be violated, a rule specifies a where condition that must be met. Thus, the integrity condition (salary <= 50000), and the rule where condition (shown in the step above), are opposites.
4. For Procedure Name, enter the name of the database procedure to execute when this rule is fired (for example, salary_too_big). This procedure must exist when the rule is created.
5. For Parameters, enter any parameters required by the salary_too_big procedure.
The specified database procedure is executed and sent any specified parameters when the salary column has a value that is too big (that is, greater than $50,000). The procedure can be written to reject the operation, causing it to be rolled back; log the error, but allow the operation; or modify the value so that it is less than or equal to $50,000, and log that action.
Last modified date: 11/09/2022