MySQL Cookbook, 4th Edition

Chapter 11. Using Stored Routines, Triggers, and Scheduled Events

11.0 Introduction

In this book, the term stored program refers collectively to stored routines, triggers, and events, and stored routine refers collectively to stored functions and procedures.

This chapter discusses stored programs, which come in several varieties:

Stored functions and procedures

A stored function or procedure object encapsulates the code for performing an operation, enabling you to invoke the object easily by name rather than repeat all its code each time it’s needed. A stored function performs a calculation and returns a value that can be used in expressions just like a built-in function such as RAND(), NOW(), or LEFT(). A stored procedure performs operations for which no return value is needed. Procedures are invoked with the CALL statement, not used in expressions. A procedure might update rows in a table or produce a result set that is sent to the client program.

Triggers

A trigger is an object that activates when a table is modified by an INSERT, UPDATE, or DELETE statement. For example, you can check values before they are inserted into a table or specify that any row deleted from a table should be logged to another table that serves as a journal of data changes. Triggers automate these actions.

Scheduled events

An event is an object that executes SQL statements at a scheduled time or times. Think of a scheduled event as something like a Unix cron job that runs within MySQL. For example, events can ...