The WITH CHECK Option
In order to prevent an INSERT or UPDATE operation on a view which would result in a row in the underlying base table which is not part of the derived view table, InterSystems SQL supports the WITH CHECK OPTION clause within a View definition. This clause can only be used with updateable views.
The WITH CHECK OPTION clause specifies that any INSERT or UPDATE operations on an updateable view must validate the resulting row against the WHERE clause of the view definition to make sure the inserted or modified row will be part of the derived view table.
For example, the following DDL statement defines an updateable GoodStudent view containing all Students with a high GPA (grade point average):
CREATE VIEW GoodStudent AS
SELECT Name, GPA
FROM Student
WHERE GPA > 3.0
WITH CHECK OPTION
Because the view contains a WITH CHECK OPTION, any attempt to INSERT or UPDATE a row in the GoodStudent view with a GPA value of 3.0 or less will fail (such a row would not represent a “good student”).
There are two flavors of WITH CHECK OPTION:
-
WITH LOCAL CHECK OPTION means that only the WHERE clause of the view specified in the INSERT or UPDATE statement is checked.
-
WITH CASCADED CHECK OPTION (and WITH CASCADE CHECK OPTION) means that the WHERE clause of the view specified in the INSERT or UPDATE statement as well as ALL views on which that view is based are checked, regardless of the appearance or absence of other WITH LOCAL CHECK OPTION clauses in those view definitions.
The default is CASCADED if just WITH CHECK OPTION is specified.
During an UPDATE or INSERT, the WITH CHECK OPTION conditions are checked after all default values and triggered computed fields have been calculated for the underlying table’s fields and before the regular table’s validation (required fields, data type validation, constraints, and so on).
After the WITH CHECK OPTION validation passes, the INSERT or UPDATE operation continues as if the INSERT or UPDATE was performed on the base table itself. All constraints are checked, triggers pulled, and so on.
If the %NOCHECK option is specified on the INSERT or UPDATE statement, the WITH CHECK OPTION validation is not checked.
There are two SQLCODE values related to the WITH CHECK OPTION validation (the INSERT/UPDATE would have resulted in a row not existing in the derived view table):
