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.