MySQL® Administrator's Bible

Chapter 7. Stored Routines, Triggers, and Events

IN THIS CHAPTER

• Comparing stored routines, triggers, and events

• Using triggers

• Using stored routines

• Using cursors

• Using events

Stored routines, triggers, and events are all ways to store SQL commands as part of the database server. These types of stored SQL enable different applications to easily use the same set of queries. Many database developers use stored SQL to build a library to give to application developers. Stored routines, triggers, and events can make the schema transparent to an application developer, which is very convenient when the schema needs to change.

Comparing Stored Routines, Triggers, and Events

MySQL has four different types of stored SQL: triggers, events, stored procedures, and stored functions. A trigger is invoked automatically when an SQL statement changes rows on a specified table. An event is invoked automatically at a predetermined time, and can be a one-time occurrence or a regular occurrence. A stored procedure is invoked manually with the CALL statement, taking zero or more arguments, and can pass back values through output variables. A stored function is invoked manually by directly using its name, taking zero or more arguments and outputting a scalar value.

A user-defined function (UDF) is another way to extend MySQL by adding a function using a different programming language. How to create and add UDFs are discussed in Chapter 4. Table 7-1 summarizes the differences among triggers, events, stored procedures, ...