Database Reference
In-Depth Information
This chapter illustrates stored routines, triggers, and events by example, but due to space
limitations does not otherwise go into much detail about their extensive syntax. For
complete syntax descriptions, see the
MySQL Reference Manual
.
Scripts for the examples shown in this chapter are located in the
routines
,
triggers
, and
events
directories of the
recipes
distribution. Scripts to create example tables are located
in the
tables
directory.
In addition to the stored programs shown in this chapter, others can be found elsewhere
in this topic. See, for example, Recipes
5.6
,
6.3
,
14.8
, and
23.2
.
Stored programs used here are created and invoked under the assumption that
cook
book
is the default database. To invoke a program from another database, qualify its
name with the database name:
CALL
cookbook
.
show_version
();
Alternatively, create a database specifically for your stored programs, create them in
that database, and always invoke them qualified with that name. Remember to grant
users who will use them the
EXECUTE
privilege for that database.
Privileges for Stored Programs
When you create a stored routine (function or procedure), the following privilege re‐
quirements must be satisfied or you will have problems:
• To create or execute the routine, you must have the
CREATE
ROUTINE
or
EXECUTE
privilege, respectively.
• If binary logging is enabled for your MySQL server, as is common practice, there
are additional requirements for creating stored functions (but not stored proce‐
dures). These requirements are necessary to ensure that if you use the binary log
for replication or for restoring backups, function invocations cause the same effect
when re-executed as they do when originally executed:
— You must have the
SUPER
privilege, and you must declare either that the function
is deterministic or does not modify data by using one of the
DETERMINISTIC
,
NO
SQL
, or
READS
SQL
DATA
characteristics. (It's possible to create functions that are
not deterministic or that modify data, but they might not be safe for replication
or for use in backups.)
— Alternatively, if you enable the
log_bin_trust_function_creators
system
variable, the server waives both of the preceding requirements. You can do this
at server startup, or at runtime if you have the
SUPER
privilege.
To create a trigger, you must have the
TRIGGER
privilege for the table associated with the
trigger.