SQL Reference Guide > SQL Reference Guide > SQL Statements > CREATE RULE
Was this helpful?
CREATE RULE
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
CREATE RULE statement defines an Ingres rule. A rule executes a specified database procedure whenever a specified condition is true. For a detailed discussion of the use of rules to enforce referential integrity and security, see the Database Administrator Guide.
The CREATE RULE statement has the following format:
[EXEC SQL] CREATE RULE [schema.]rule_name table_condition
              [FOR EACH {ROW | STATEMENT}]
              EXECUTE PROCEDURE [schema.]proc_name[(parameter = value
                            {, parameter = value})]
The table_condition has the following format:
BEFORE|AFTER statement_type {, statement_type} ON|OF|FROM|INTO [schema.]tablename
     [REFERENCING [OLD AS old_corr_name] [NEW AS new_corr_name]]
     [WHERE qualification]
Note:  The keyword TRIGGER can be used as an alias for RULE.
rule_name
Specifies the name of the rule.
Rules can be defined against base tables and against views if the view is updatable. For restrictions, see CREATE VIEW.
Limits: The rule name must be a valid object name (see page Object Naming Rules on page 30) that is unique within the set of rules owned by the user issuing the CREATE RULE statement.
table_condition
Defines the action that fires the rule. For details, see Table_Condition.
FOR EACH ROW|STATEMENT
Defines a row- or statement-level rule. For details, see Row and Statement Level Rules.
proc_name
Specifies the procedure to be executed when a statement fires the rule. The procedure must exist at the time the rule is created. For details, see Procedure Execution in CREATE RULE.
parameter
Specifies one or more values to be passed to the procedure. Not all of the parameters appearing in the definition of the invoked procedure have to be included. However, those that are included must match in name and data type. Parameters can be specified using host language variables. Parameters cannot be passed by reference to a procedure that is executed by a rule.
value
Contains a constant or an old or new value in the row that caused the rule to fire. Constant values cannot contain function expressions. If value represents a row value, it must be qualified by a correlation name. For details, see the description of the referencing clause.
Embedded Usage
In an embedded CREATE RULE statement, host language variables can be used to represent the parameters of the procedure.
Permissions
To create a rule against a table, you must own the table and have EXECUTE privileges for the procedure invoked by the rule.
Once a rule is applied to a table, any user who has permission to access that table using the operation specified by the rule has permission to fire the rule and consequently execute its associated procedure.
Locking
The CREATE RULE statement takes an exclusive lock on the specified table.
Related Statements
DELETE
EXECUTE PROCEDURE
INSERT
UPDATE
Last modified date: 01/30/2023