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.
 
Search WWH ::




Custom Search