I.1. Restrictions on Stored Routines and Triggers
Some of the restrictions noted here apply to all stored routines;
that is, both to stored procedures and stored functions. Some of
restrictions apply only to stored functions, and not to stored
All of the restrictions for stored functions also apply to
triggers. In addition, triggers currently are not activated by
foreign key actions.
Stored routines cannot contain arbitrary SQL statements. The
following statements are disallowed:
The locking statements
LOAD DATA and
SQL prepared statements (
PREPARE). Implication: You cannot use dynamic SQL
within stored routines (where you construct dynamically
statements as strings and then execute them). This restriction
is lifted as of MySQL 5.0.13 for stored procedures; it still
applies to stored functions and triggers.
For stored functions (but not stored procedures), the following
additional statements or operations are disallowed:
Statements that do explicit or implicit commit or rollback.
Statements that return a result set. This includes
SELECT statements that do not have an
SHOW statements. A function can
process a result set either with
SELECT ... INTO
or by using a
FETCH statements. See
Section 126.96.36.199, “
SELECT ... INTO Statement”.
Recursive statements. That is, stored functions cannot be used
Within a stored function or trigger, it is not allowable to
modify a table that is already being used (for reading or
writing) by the statement that invoked the function or
Note that although some restrictions normally apply to stored
functions and triggers but not to stored procedures, those
restrictions do apply to stored procedures if they are invoked
from within a stored function or trigger. For example, although
you can use
FLUSH in a stored procedure, such a
stored procedure cannot be called from a stored function or
It is possible for the same identifier to be used for a routine
parameter, a local variable, and a table column. Also, the same
local variable name can be used in nested blocks. For example:
CREATE PROCEDURE p (i INT)
DECLARE i INT DEFAULT 0;
SELECT i FROM t;
DECLARE i INT DEFAULT 1;
SELECT i FROM t;
In such cases the identifier is ambiguous and the following
precedence rules apply:
A local variable takes precedence over a routine parameter or
A routine parameter takes precedence over a table column
A local variable in an inner block takes precedence over a
local variable in an outer block
The behavior that table columns do not take precedence over
variables is non-standard.
Use of stored routines can cause replication problems. This issue
is discussed further in
Section 19.4, “Binary Logging of Stored Routines and Triggers”.
INFORMATION_SCHEMA does not yet have a
PARAMETERS table, so applications that need to
acquire routine parameter information at runtime must use
workarounds such as parsing the output of
There are no stored routine debugging facilities.
CALL statements cannot be prepared. This true
both for server-side prepared statements and for SQL prepared
UNDO handlers are not supported.
FOR loops are not supported.
To prevent problems of interaction between server threads, when a
client issues a statement, the server uses a snapshot of routines
and triggers available for execution of the statement. That is,
the server calculates a list of procedures, functions, and
triggers that may be used during execution of the statement, loads
them, and then proceeds to execute the statement. This means that
while the statement executes, it will not see changes to routines
performed by other threads.
RETURN statement is disallowed in triggers,
which cannot return a value. To exit a trigger immediately, use
RENAME DATABASE statement does not migrate
stored routines to the new schema name. See
Section 13.1.9, “
RENAME DATABASE Syntax”.