MySQL/Stored Programs

MySQL supports some procedural extensions to SQL. By using them, you can manage the control flow, create loops and use cursors. These features allow you to create stored programs, which may be of 3 kinds:


 * Triggers - programs which are triggered before / after a certain event involves a table (DELETE, INSERT, UPDATE);
 * Events - programs which are executed regularly after some time intervals;
 * Stored Procedures - programs which can be called via the CALL SQL command.

MySQL future versions will support stored program written in other languages, not only SQL. You will have the ability to manage new languages as PLUGINs. Also, the stored procedures will be compiled into C code, and thus they will be faster.

Managing Triggers
Triggers were added in MySQL 5.0.2. They work on persistent tables, but can't be associated with TEMPORARY tables.

CREATE TRIGGER
To create a new trigger:

This example trigger defines a stored program (which is the simple DELETE statement) called `delete_old`. It's automatically fired when a new record is INSERTed into `articles`. It's called after the INSERT, not before. If a single INSERT adds more than one row to the table, `delete_old` is called more than once. The idea is simple: when a new record is created, the oldest record is DELETEd.

A trigger may be executed BEFORE or AFTER a certain SQL statement. This is important because a trigger may execute one or more statements which activate other triggers; so, it may be important to decide their time order, to ensure the database's integrity.

The statement which fires the trigger must be a basic DML command:
 * INSERT, which includes LOAD DATA and REPLACE
 * DELETE, which includes REPLACE, but not TRUNCATE
 * UPDATE

A special case is INSERT ... ON DUPLICATE KEY UPDATE. If the INSERT is executed, both BEFORE INSERT and AFTER INSERT are executed. If the INSERT is not executed, and thus an UPDATE is executed instead, the order of events is the following: BEFORE INSERT, BEFORE UPDATE, AFTER UPDATE.

You can also specify the table's name by using the following syntax: ... ON `my_database`.`my_table` ...

Triggers' names must be unique in a database. Two tables located in the same database can't be associated to two different triggers with the same name.

Unlike other DBMSs and standard SQL, all triggers are fired FOR EACH ROW, and can't be executed for each statement.

A stored program must be specified between BEGIN and END reserved words. You can't use dynamic SQL here (the PREPARE statement); use can call a stored procedure, instead. If you execute only one statement, you can omit the BEGIN and END words.

You can access to the old value of a field (the value it has before the execution of the statement) and to the new value (the value it has after the execution of the statement. Example:

DROP TRIGGER
To DROP a trigger you can use the following syntax: Or: Or:

To alter an existing trigger, you must DROP and re-CREATE it.

SHOW CREATE TRIGGER
This command returns the CREATE TRIGGER statement used to create the trigger and some information about the settings which may affect the statement.


 * Trigger - Trigger name
 * sql_mode - The value of SQL_MODE at the time of the execution of the statement
 * SQL Original Statement
 * character_set_client
 * collation_connection
 * Database Collation

This statement was added in MySQL 5.1.

SHOW TRIGGERS
If you want to have a list of all the triggers in the current database, you can type the following: If you want to have a list of the triggers contained in another database, you can use: If you want to list the triggers whose name matches to a LIKE expression: More complex filters: You can't use LIKE and WHERE together.

The columns returned by this statement are:


 * Trigger - Trigger's name
 * Event - The SQL command that fires the trigger
 * Table - The table that is associated to the trigger
 * Statement - The statement that is executed by the trigger
 * Timing - BEFORE or AFTER
 * Created - It's always NULL
 * sql_mode - The SQL_MODE which was set when the trigger was created
 * Definer - The user who created the trigger
 * character_set_client - The value of the `character_set_client` variable when the trigger was created
 * collation_connection - The value of the `collation_connection` variable when the trigger was created
 * Database Collation - The COLLATION used by the database (and the trigger)

INFORMATION_SCHEMA.TRIGGERS
The INFORMATION_SCHEMA virtual database has a `TRIGGERS` table. It has the following fields:


 * TRIGGER_CATALOG - What catalog contains the trigger (not implemented yet)
 * TRIGGER_SCHEMA - What SCHEMA (DATABASE) contains the trigger
 * TRIGGER_NAME - Trigger's name
 * EVENT_MANIPULATION - INSERT / UPDATE /DELETE
 * EVENT_OBJECT_CATALOG - Not implemented yet
 * EVENT_OBJECT_SCHEMA - SCHEMA containing the table associated to the trigger
 * EVENT_OBJECT_NAME - Name of the table associated to the trigger
 * ACTION_ORDER - Not implemented yet
 * ACTION_CONDITION - Not implemented yet
 * ACTION_STATEMENT - Statement(s) to be executed when trigger activates
 * ACTION_ORIENTATION - Not implemented yet
 * ACTION_TIMING - BEFORE / AFTER
 * ACTION_REFERENCE_OLD_TABLE - Not implemented
 * ACTION_REFERENCE_NEW_TABLE - Not implemented
 * ACTION_REFERENCE_OLD_ROW - Not implemented
 * ACTION_REFERENCE_NEW_ROW - Not implemented
 * CREATED - Creation time (not implemented yet)
 * SQL_MODE - SQL_MODE valid for this trigger's execution
 * DEFINER - User who created the trigger, in the form 'user@host'
 * CHARACTER_SET_CLIENT - The value of the `character_set_client` variable when the trigger was created
 * COLLATION_CONNECTION - The value of the `collation_connection` variable when the trigger was created
 * DATABASE_COLLATION - The COLLATION used by the database (and the trigger)

Events
Events are also called Scheduled Events or Temporal Triggers. They are planned events which are executed at certain times, or at specified time intervals. They are similar to the UNIX.

Once an Event is started, it must be completely executed. If it is re-activated before it ends its execution, a new instance of the same Event will be created. If this can happen, it may be a good idea to use LOCKs to assure data consistence.

The Event Scheduler is a thread which is permanently in execution. It starts the Events when they must be started. If you don't need Events, you can disable the Event Scheduler. You can do this starting MySQL with the following option: mysqld --event-scheduler=DISABLED Or you can add a line to the my.cnf configuration file: event_scheduler=DISABLED

If the Event Scheduler is not disabled, you will be able to turn it ON/OFF runtime. It is controlled by a global system variable:

If the Event Scheduler is ON, you can check its status with SHOW PROCESSLIST. It is shown like all other threads. Its `User` is 'event_scheduler'. When it is sleeping, the value for `State` is 'Waiting for next activation'.

Managing Events
You can use the SQL commands CREATE EVENT, ALTER EVENT and DROP EVENT.

CREATE EVENT
The simplest case. We want a SQL command to be executed tomorrow:

The event name must be specified after "EVENT".

If you want to create a task which will be executed only once at a certain time, you need the AT clause. If you don't want to specify an absolute time, but we want the task to be executed when a time interval is passed, "AT CURRENT_TIMESTAMP + INTERVAL ..." is a useful syntax.

If you want to create a recurring task (which will be executed at regular intervals) you need the EVERY clause:

You can also specify a start time and/or an end time. The task will be executed at regular intervals from the start time until the end time:

The allowed time units are:

The DO clause specify which statement must be executed.

If a task is composed by more than 1 statement, the BEGIN ... END syntax must be used:

If an EVENT with the same name already exists you get an error from the server. To suppress the error, you can use the IF NOT EXISTS clause:

After the EVENT is expired (when the timestamp specified in the AT clause or in the ENDS clause), MySQL drops the event by default, as it is no more useful. You may want to preserve it from deletion to ALTER it someday and activate it again, or just to have its code somewhere. You may do this with the ON COMPLETION clause:

Or, you can explicitly tell MySQL to drop it, even if it's not necessary:

If you don't tell MySQL to preserve the EVENT after it's expired, but it is already expired immediatly after creation (which happens if you specify a past TIMESTAMP in the AT / ENDS clause), the server creates and drop it as you requested. However, in this case it will inform you returning a 1588 warning.

You can also specify if an EVENT must be enabled. This is done by specifying ENABLE, DISABLE or DISABLE ON SLAVES (used to execute the event on the master and not replacate it on the slaves). The EVENT is enabled by default.

To modify this behaviour, you will use ALTER EVENT.

You can specify a comment for the EVENT. Comments have a 64 characters limit. The comment must be a literal, not an expression. Example:

You can also specify which user must be used to check privileges during the execution of the EVENT. By default, the CURRENT_USER is used. You can specify that explicitly:

To specify a different user, you must have the SUPER privilege. In that case, you must specify both the username and the host:

ALTER EVENT
The ALTER EVENT statement can be used to modify an existing EVENT.

RENAME TO is used to rename the EVENT.

You only need to specify the clauses that you want to change:

DROP EVENT
You need the EVENT privilege to drop an event. To drop an event you can type:

If the EVENT does not exist, you get a 1517 error. To avoid this, you can use the IF EXISTS clause:

If the EVENT needs to be executed only once or just for a known time period, by default MySQL drops it automatically when it is expired (see the ON COMPLETE clause in CREATE EVENT).

SHOW CREATE EVENT
This command returns the CREATE EVENT statement used to create the trigger and some information about the settings which may affect the statement.

Syntax:


 * Event - Event name.
 * sql_mode - SQL mode which was in effect when the CREATE EVENT statement was executed.
 * time_zone - Time zone that was used when the statement was executed.
 * Create Event - Statement used to create the event.
 * character_set_client
 * collation_connection
 * Database Collation

SHOW EVENTS
The statement shows information about the EVENTs which are in the current database or in the specified database:


 * Db Database name.
 * Name Event name.
 * Definer User which created the EVENT and the host he used, in the form user@host.
 * Time zone Timezone in use for the EVENT. If it never changed, it should be 'SYSTEM', which means: server's timezone.
 * Type 'ONE TIME' for EVENTs which are executed only once, 'RECURRING' for EVENTs which are executed regularly.
 * Executed At The TIMESTAMP of the moment the EVENT will be executed. NULL for recursive EVENTs.
 * Interval Value Number of intervals between EVENT's executions. See next field. NULL for EVENTs which are executed only once.
 * Interval Field Interval type to wait between EVENTs executions. For example, if `Interval Field` is 'SECOND' and `Interval Value` is 30, the EVENT will be executed every 30 seconds. NULL for EVENTs which are executed only once.
 * Starts First execution DATETIME for recurring EVENTs. NULL for events which are executed only once.
 * Ends Last execution DATETIME for recurring EVENTs. NULL for events which are executed only once.
 * Status ENABLED, DISABLED, or SLAVESIDE_DISABLED. For ENABLED and DISABLED, see above. SLAVESIDE_DISABLED was added in 5.1 and means that the EVENT is enabled on the master but disabled on the slaves.
 * Originator Id of the server where the EVENT was created. If it has been created on the current server this value is 0. Added in 5.1.
 * character_set_client
 * collation_connection
 * Database Collation

INFORMATION_SCHEMA.EVENTS
The INFORMATION_SCHEMA virtual database has a `EVENTS` table. It's non-standard and has been added in 5.1. EVENTS has the following fields:


 * EVENT_CATALOG Always NULL (CATALOGs are not implemented in MySQL).
 * EVENT_SCHEMA Database name.
 * EVENT_NAME Event name.
 * DEFINER User which created the EVENT and the host he used, in the form user@host.
 * TIME_ZONE Timezone in use for the EVENT. If it never changed, it should be 'SYSTEM', which means: server's timezone.
 * EVENT_BODY Language used to write the routine that will be executed.
 * EVENT_DEFINITION Routine that will be executed.
 * EVENT_TYPE 'ONE TIME' for EVENTs which are executed only once, 'RECURRING' for EVENTs which are executed regularly.
 * EXECUTE_AT The TIMESTAMP of the moment the EVENT will be executed. NULL for recursive EVENTs.
 * INTERVAL_VALUE Number of intervals between EVENT's executions. See next field. NULL for EVENTs which are executed only once.
 * INTERVAL_FIELD Interval type to wait between EVENTs executions. For example, if `Interval Field` is 'SECOND' and `Interval Value` is 30, the EVENT will be executed every 30 seconds. NULL for EVENTs which are executed only once.
 * SQL_MODE SQL mode which was in effect when the EVENT has been created.
 * STARTS First execution DATETIME for recurring EVENTs. NULL for events which are executed only once.
 * ENDS Last execution DATETIME for recurring EVENTs. NULL for events which are executed only once.
 * STATUS ENABLED, DISABLED, or SLAVESIDE_DISABLED. For ENABLED and DISABLED, see above. SLAVESIDE_DISABLED was added in 5.1 and means that the EVENT is enabled on the master but disabled on the slaves.
 * ON_COMPLETION 'NOT PRESERVE' (the EVENT will be deleted) or 'PRESERVE' (the EVENT won't be deleted'.
 * CREATED Creation DATETIME.
 * LAST_ALTERED Last edit's DATETIME. If the EVENT has never been altered, `LAST_ALTERED` has the same value as `CREATED`.
 * LAST_EXECUTED Last execution TIMESTAMP. If the EVENT has never been executed yet, this value is NULL.
 * EVENT_COMMENT Comment associated to the EVENT. Is there is no comment, this value is an empty string.
 * ORIGINATOR Id of the server where the EVENT was created. If it has been created on the current server this value is 0. Added in 5.1.
 * character_set_client
 * collation_connection
 * Database Collation

Stored Routines
Stored Routines are modules written in SQL (with some procedural extensions) which may be called within another statement, using the CALL command.

Stored Routines are called FUNCTIONs if they return a result, or PROCEDUREs if they don't return anything. STORED PROCEDUREs must not be confused with the PROCEDUREs written in C or LUA which can be used in a SELECT statement; STORED FUNCTIONs must not be confused with UDF, even if they both are created with a CREATE FUNCTION statement.

Advantages of Stored Routines

 * They reduce network traffic: they may contain many statements, but only one statement need to be sent to invoke them.
 * Ability to keep the logic within the database.
 * Reusable modules which can be called from external programs, no matter in what language they are written.
 * You can modify the Stored Routines without changing your programs.
 * The user which invokes a Stored Routine doesn't need to have access to the tables which it reads / writes.
 * Calling Stored Routines are faster than executing single statements.

INFORMATION_SCHEMA.ROUTINES
The virtual database INFORMATION_SCHEMA has a table called `ROUTINES`, with the functions and procedures information.

INFORMATION_SCHEMA.PARAMETERS
This table contains all the stored functions values.

Delimiter
MySQL uses a character as delimiter - MySQL knows that where that character occurs a SQL statement ends and possibly another statement begins. That character is ';' by default. When you create a stored program which contains more than one statements, you enter only one statement: the CREATE command. However, it contains more then one statements in its body, separated with a ';'. In that case, you need to inform MySQL that ';' does not identify the end of the CREATE statement: you need another delimiter.

In the following example, '|' is used as a delimiter:

Flow control
The keywords are:.

Cursors
The allow to treat each row differently, but it considerably slows the queries.

They should be declared and open before the loop which should treat every records differently. To know the table end, we should create a handler after the cursor:

Error handling
A handler declaration permits to specify a treatment in case of error :

Moreover, the error type can be indicated: