Filter
Top Products
· Chained triggers cannot be executed by updating the triggered SQL statement specified by an update-and-add
trigger.
· If the trigger defined for the table updated by the triggered SQL statement is a trigger other than an
update-and-add trigger, a chain of triggers is executed. If a trigger is executed again as an extension of its own
execution processing, an error occurs.
· A triggered SQL statement operates as the transaction that executes the SQL statement starting the trigger. If
LOCK_MODE is specified in the SQL statement that starts a trigger, the triggered SQL statement operates,
assuming that the same LOCK_MODE is implicitly specified in each SQL data manipulation statement
executed by the triggered SQL statement.
· When a database is updated by a triggered SQL statement, the unique and NOT NULL constraints defined for
an updated table are checked in each SQL statement. If the CALL statement is specified, the constraints are
checked in each SQL statement defined in the CALL statement.
· If an error occurs in a triggered SQL statement, execution of the SQL statement that has started the trigger is
canceled with an error. If the CALL statement is specified as the triggered SQL statement, an error may occur
during execution of an SQL statement in the called procedure routine. In this event, execution of SQL
statements can continue in accordance with the error handling method specified in the procedure routine.
Alternatively, execution of the CALL statement can be assumed to cause the error, and execution of the SQL
statement that starts the trigger can be canceled with an error.
· If the CALL statement is specified as a triggered SQL statement, a transaction rollback exception may occur in
the called procedure routine (SQLSTATE exception code: 40). In this event, the transaction containing the SQL
statement that starts the trigger is automatically rolled back.
Notes on defining a trigger
Note the following points about defining a trigger.
· If a subquery is specified in an SQL statement that starts a trigger, the table specified by the subquery cannot
be updated by the triggered SQL statement.
· If a row with a unique constraint in the table has been updated using the search routine of the UPDATE
statement, using an SQL statement for updating multiple rows may temporarily cause some data to not be
unique. When data is not unique, the row whose updating causes the start of the trigger cannot be referenced
temporarily in the extension of the CALL statement specified as the triggered SQL statement of the executed
trigger. For this reason, if the procedure routine called by the CALL statement must reference the row whose
update causes the start of the trigger, specify the appropriate arguments of the CALL statement to pass the
necessary row values.
· Privileges required when a trigger is defined
The user who wants to define a trigger must have the CREATE privilege for the schema for which the trigger is
defined, TRIGGER privilege for the table for which the trigger is defined, and privilege corresponding to the
SQL operation specified in the triggered SQL statement.
- To specify the INSERT statement: User must have the INSERT privilege for the table specified in the
triggered SQL statement.
- To specify the DELETE statement: User must have the DELETE privilege for the table specified in the
triggered SQL statement.
- To specify the UPDATE statement: User must have the UPDATE privilege for the table specified in the
triggered SQL statement.
- To specify the CALL statement: User must have the execution privilege for the procedure routine
specified in the triggered SQL statement.
· Privilege check when a triggered SQL statement is executed
When an SQL statement starts a trigger and the triggered SQL statement is executed, no privilege check is
performed for the triggered SQL statement. The user who executes the application program does not need the
privilege for the table specified in the triggered operation.
2.6.7 Procedure routine definition
Define a procedure routine using the CREATE PROCEDURE statement. For details about the procedure function,
refer to the "RDB User's Guide: Application Program Development." A sample definition that sets procedure PROC001
in the STOCKMN_DB database follows.
Example:
Define PROC001.
48