This chapter explains how to store SQL procedures for future use and how to create triggers. For information about stored views, see Chapter 16, Retrieving Data.

This chapter includes the following sections:

Using stored procedures, you can group logically associated programming steps into a general process and then invoke that process with one statement. You can also execute this process using different values by passing parameters.

Once invoked, SQL stored procedures are executed in their entirety without internal communication between a host language program and the SQL engine. You can invoke them independently, and they can be invoked as part of the body of other procedures or triggers. For more information about triggers, see SQL Triggers.

You can use SQL variable statements within stored procedures to store values internally from statement to statement. See SQL Variable Statements for more information about these statements.

You can use SQL control statements in stored procedures to control the execution flow of the procedure. For more information about these statements, see SQL Control Statements.

The following is an example of a stored procedure and positioned update:

To define a stored procedure, use the CREATE PROCEDURE statement.

The maximum size for a stored procedure name is 30 characters. Parentheses are required around the parameter list, and the parameter name may be any valid SQL identifier.

Stored procedures must have unique names in the dictionary.

For information about the syntax of the CREATE PROCEDURE statement, see CREATE PROCEDURE in SQL Engine Reference.

To invoke a stored procedure, use the CALL statement.

You must define a value for every parameter. You can assign a value to a parameter using the associated argument in the CALL statement or with the associated default clause in the CREATE PROCEDURE statement. An argument value for a parameter in a CALL statement overrides any associated default value.

You can specify calling values in a CALL statement using either of the following two ways:

You cannot assign a parameter value twice in the argument list (either positional or keyword). If you use both positional arguments and keyword arguments in the same call, the keyword arguments must not refer to a parameter that receives its value through the positional arguments. When using keyword arguments, the same parameter name must not occur twice.

For syntax information, see CALL in SQL Engine Reference.

To delete a stored procedure, use the DROP PROCEDURE statement.

For syntax information, see DROP PROCEDURE in SQL Engine Reference.

SQL variables use SET to assign values that are accessible from statement to statement. You can use SET statements inside stored procedures. These statements are called assignment statements.

An SQL variable you define inside a stored procedure is a procedure-owned variable. Its scope is the procedure in which it is declared, so you can reference it only within that procedure. If a procedure calls another procedure, the procedure-owned variable of the calling procedure cannot be directly used in the called procedure and instead must be passed in a parameter. You cannot declare a procedure-owned variable more than once in the same stored procedure.

If a compound statement is the body of a stored procedure, then no SQL variable name declared in that procedure can be identical to a parameter name in the parameter list of that procedure. For more information, see Compound Statement.

An assignment statement initializes or changes the values of SQL variables. The value may be a computed expression involving constants, operators, and this or other SQL variables.

The value expression may also be a SELECT statement.

For syntax information, see SET in SQL Engine Reference.

You can only use control statements in the body of a stored procedure. These statements control the execution of the procedure. The control statements include the following:

A compound statement groups other statements together.

You can use a compound statement in the body of a stored procedure or a trigger. For more information, see SQL Triggers.

Although you can nest compound statements within other compound statements, only the outermost compound statement can contain DECLARE statements.

For information about compound statement syntax, see BEGIN [ATOMIC] in SQL Engine Reference.

An IF statement provides conditional execution based on the truth value of a condition.

For syntax information, see IF in SQL Engine Reference.

A LEAVE statement continues execution by leaving a compound statement or loop statement.

A LEAVE statement must appear inside a labeled compound statement or a labeled loop statement. The statement label from the LEAVE statement must be identical to the label of a labeled statement containing LEAVE. This label is called the corresponding label.

For syntax information, see LEAVE in SQL Engine Reference.

A LOOP statement repeats the execution of a block of statements.

If each statement in the SQL statement list executes without error and PSQL does not encounter a LEAVE statement or invoke a handler, then execution of the LOOP statement repeats. A LOOP statement is similar to a WHILE statement in that execution continues while a given condition is true.

If a LOOP statement has a beginning label, it is called a labeled LOOP statement. If you specify the ending label, then it must be identical to the beginning label.

For syntax information, see LOOP in SQL Engine Reference.

A WHILE statement repeats the execution of a block of statements while a specified condition is true.

PSQL evaluates the Boolean value expression. If it is true, then PSQL executes the SQL statement list. If each statement in the SQL statement list executes without error and no LEAVE statement is encountered, then execution of the loop statement repeats. If the Boolean value expression is false or unknown, PSQL terminates execution of the loop statement.

If a WHILE statement has a beginning label, it is called a labeled WHILE statement. If you specify an ending label, it must be identical to the beginning label. For syntax information, see WHILE in SQL Engine Reference.

Triggers are actions defined on a table that you can use to enforce consistency rules for a database. They are dictionary objects that identify the appropriate action for the DBMS to perform when a user executes a SQL data modification statement on that table.

To declare a trigger, use the CREATE TRIGGER statement.

The maximum size for a trigger name is 30 characters.

To delete a trigger, use the DROP TRIGGER statement.

You cannot invoke a trigger directly; they are invoked as a consequence of an INSERT, UPDATE, or DELETE action on a table with an associated trigger. For syntax information, see the following topics in SQL Engine Reference:

Since triggers execute automatically for a given event, it is important to be able to specify when and in what order the trigger or triggers should execute. You specify time and order when you create the trigger.

When an event that is associated with a trigger occurs, the trigger must execute either before the event or after the event. For example, if an INSERT statement invokes a trigger, the trigger must execute either before the INSERT statement executes or after the INSERT statement executes.

You must specify either BEFORE or AFTER as the triggered action time. The triggered action executes once for each row. If you specify BEFORE, the trigger executes before the row operation; if you specify AFTER, the trigger executes after the row operation.

You may have situations in which an event invokes more than one trigger for the same specified time. For example, an INSERT statement may invoke two or more triggers that are defined to execute after the INSERT statement executes. Since these triggers cannot execute simultaneously, you must specify an order of execution for them.

Since the following CREATE TRIGGER statement specifies an order of 1, any subsequent BEFORE INSERT triggers that you define for the table must have a unique order number greater than 1.

You designate the order value with an unsigned integer, which must be unique for that table, time, and event. If you anticipate inserting new triggers within the current order, leave gaps in the numbering to accommodate this.

If you do not designate an order for a trigger, then the trigger is created with a unique order value that is higher than that of any trigger currently defined for that table, time, and event.

The trigger action executes once for each row. The syntax for the trigger action is as follows:

If the triggered action contains a WHEN clause, then the triggered SQL statement executes if the Boolean expression is true. If the expression is not true, then the triggered SQL statement does not execute. If no WHEN clause is present, then the triggered SQL statement executes unconditionally.

The triggered SQL statement can be either a single SQL statement, including a stored procedure call (CALL procedure_name), or a compound statement (BEGIN...END).

When you need to reference a column of the old row image (in the case of DELETE or UPDATE) or a column of the new row image (in the case of INSERT or UPDATE) in the triggered action, you must add a REFERENCING clause to the trigger declaration, as follows:

The REFERENCING clause allows you to maintain information about the data that the trigger modifies.