Chapter 25 Stored Objects Table of Contents 25.1 Defining Stored Programs ..................................................................................................... 4728 25.2 Using Stored Routines .......................................................................................................... 4729 25.2.1 Stored Routine Syntax ................................................................................................ 4730 25.2.2 Stored Routines and MySQL Privileges ....................................................................... 4730 25.2.3 Stored Routine Metadata ............................................................................................ 4731 25.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID() ................................. 4731 25.3 Using Triggers ...................................................................................................................... 4732 25.3.1 Trigger Syntax and Examples ..................................................................................... 4732 25.3.2 Trigger Metadata ........................................................................................................ 4736 25.4 Using the Event Scheduler .................................................................................................... 4737 25.4.1 Event Scheduler Overview .......................................................................................... 4737 25.4.2 Event Scheduler Configuration .................................................................................... 4738 25.4.3 Event Syntax .............................................................................................................. 4740 25.4.4 Event Metadata .......................................................................................................... 4740 25.4.5 Event Scheduler Status .............................................................................................. 4741 25.4.6 The Event Scheduler and MySQL Privileges ................................................................ 4742 25.5 Using Views .......................................................................................................................... 4744 25.5.1 View Syntax ............................................................................................................... 4745 25.5.2 View Processing Algorithms ........................................................................................ 4745 25.5.3 Updatable and Insertable Views .................................................................................. 4747 25.5.4 The View WITH CHECK OPTION Clause .................................................................... 4749 25.5.5 View Metadata ........................................................................................................... 4750 25.6 Stored Object Access Control ................................................................................................ 4750 25.7 Stored Program Binary Logging ............................................................................................. 4754 25.8 Restrictions on Stored Programs ........................................................................................... 4761 25.9 Restrictions on Views ............................................................................................................ 4764 This chapter discusses stored database objects that are defined in terms of SQL code that is stored on the server for later execution. Stored objects include these object types: •Stored procedure: An object created with  CREATE PROCEDURE  and invoked using the  CALL  statement. A procedure does not have a return value but can modify its parameters for later inspection by the caller. It can also generate result sets to be returned to the client program. •Stored function: An object created with  CREATE FUNCTION  and used much like a built-in function. You invoke it in an expression and it returns a value during expression evaluation. •Trigger: An object created with  CREATE TRIGGER  that is associated with a table. A trigger is activated when a particular event occurs for the table, such as an insert or update. •Event: An object created with  CREATE EVENT  and invoked by the server according to schedule. •View: An object created with  CREATE VIEW  that when referenced produces a result set. A view acts as a virtual table. •Stored routines include stored procedures and functions. Terminology used in this document reflects the stored object hierarchy: 4727