ALLBASE/SQL Reference Manual (36216-90216)

Chapter 11 455
SQL Statements E - R
INSERT
• Rules defined with a StatementType of INSERT will affect all kinds of INSERT
statements performed on the rules' target tables. When the INSERT is performed,
ALLBASE/SQL considers all the rules defined for that table with the INSERT
StatementType . If the rule has no condition, it will fire for all rows affected by the
statement and invoke its associated procedure with the specified parameters on each
row. If the rule has a condition, it will evaluate the condition on each row. The rule will
fire on rows for which the condition evaluates to TRUE and invoke the associated
procedure with the specified parameters for each row. Invoking the procedure could
cause other rules, and thus other procedures, to be invoked if statements within the
procedure trigger other rules.
• If a DISABLE RULES statement is in effect, the INSERT statement will not fire any
otherwise applicable rules. When a subsequent ENABLE RULES is issued, applicable
rules will fire again, but only for subsequent INSERT statements, not for those rows
processed when rule firing was disabled.
• In a rule defined with a
StatementType of INSERT, any column reference in the
Condition or any ParameterValue will refer to the value of the column as it is
assigned in the INSERT statement, or by the default value of the column if it is not
included in the INSERT statement.
• The set of rows to be inserted by a type 2 INSERT (that is, an INSERT defined by a
SELECT statement) is determined before any rule fires, and this set remains fixed until
the completion of the rule. In other words, if the rule adds to, deletes from, or modifies
this set, such changes are ignored.
• When a rule is fired by this statement, the rule's procedure is invoked after the changes
have been made to the database for that row and all previous rows. The rule's
procedure, and any chained rules, will thus see the state of the database with the
current partial execution of the statement.
• If an error occurs during processing of any rule considered during execution of this
statement (including execution of any procedure invoked due to a rule firing), the
statement and any procedures invoked by any rules will have no effect. Nothing will
have been altered in the DBEnvironment as a result of this statement or the rules it
fired. Error messages are returned in the normal way.
Authorization — INSERT Rows Defined by a SELECT Command
(Type 2 Insert)
To insert rows into a table, you must have INSERT or OWNER authority for that table or
you must have DBA authority.
To insert rows using a view, you must have INSERT or OWNER authority for that view or
you must have DBA authority. Also, the owner of the view must have INSERT or OWNER
authority with respect to the view's base tables, or the owner must have DBA authority.
If you specify the name of a table in the FROM clause of the SELECT command, you must
have SELECT or OWNER authority for the table or you must have DBA authority. If you
specify the name of a view in the FROM clause of the SELECT command, you must have
SELECT or OWNER authority for the view or you must have DBA authority. Also, the
owner of the view must have SELECT or OWNER authority with respect to the view's
definition, or the owner must have DBA authority.