A validation rule has three possible Actions:
|
ACCEPT |
Accept this validation and all follow-on validations. Do not return an error. The rule may not involve a value and there may be more than one rule. |
|
ERROR |
The validation failed return an error. |
|
NEXT |
Accept this validation but check follow-on validations if any exist. |
See Validation Actions for more information.
Here we show how two rules for a column can be combined to produce rule:
"accept if date column has SQLNULL value, but validate if date has non SQLNULL value"
1. If you have not yet completed How to Create a Table, complete the tutorial now to produce a table for this exercise.
2. Open table MyEmployee in the editor and select the Rules and Triggers tab.
3. Expand the rules for column xEmployeeDateofBirth. If you completed Add a Quick Rule to a Table, you will see this:
4. If you do not have the Before Today rule, add it now. With field xEmployeeDateofBirth selected, from the Add menu, select Quick Rules / Before Today. Change the Validation Usage to "Apply when column is used (CHGUSE)". Your rule should look like the validation rule shown above.
5. Select the xEmployeeDateofBirth column and from the Add menu, select Quick Rule / Cannot be SQLNull to add a second rule to this column:
6. With the Cannot be SQLNull rule selected on the Rules and Triggers tab, move it before the Before Today rule using the Up Arrow toolbar button:
Rules for xEmployeeDateofBirth should now look like this:
7. Next modify the Cannot be SQLNULL rule so that this rule accepts xEmployeeDateofBirth if its value is equal to SQLNULL.
If necessary double-click Cannot be SQLNULL for column xEmployeeDateofBirth on the Rules and Triggers tab to open the details on the Details tab.
Change the rule as follows:
|
Description |
Accept when SQLNULL |
|
Condition is true |
Value is accepted (ACCEPT) |
|
Condition is false |
Evaluate next rule (NEXT) |
This change has the following effect:
o When the date value is SQLNULL, the value is accepted and no further validation checks are performed on this column.
o When the date value is not SQLNULL, the Before Today validation rule will be performed.
8. Compile the table. Review Compile Options to ensure only the Rebuild OAMs option is checked.
When an update or insert is made to the Employee table, if Date of Birth is SQLNULL, the value will be accepted. If Data of Birth is not SQLNULL, the date must be before today's date or an error will be raised.