MySQL Stored Routines, Triggers, Cursors and Events

MySQL

Stored routines, triggers and events enable different applications to use the same set of queries, developers build libraries of SQL code that is stored and can be used by other developers and applications. There are four types of stored SQL

Triggers
Stored Procedures
Stored Functions
Events

–MySQL allows for a maximum of six triggers per table

before insert
after insert
before update
after update
before delete
after delete

A stored routine (procedure or function) allows a user to define a set of statements that they can call later in a query, they make repetitive tasks involving many queries easier. The reasons why you would use a store routine are

Code Reuse – different applications can use the same stored routines, instead of using repetitive code
Black Box queries – developers can call routines without having to know the code inside, they just need to know what goes in and what should come out
Security via API – auditing functionality and logging can be implemented inside the stored routine
Security via ACL constraints – additional privileges can be given to the stored routine, allowing users access to tables and columns.

Handlers

MySQL allows you to specify handlers that can trap conditions (basically they are event listeners), this allows you to handle known exceptions.

Cursors

Developers like to be able to go through a data set one by one and this is where cursors can help, it can be thought of as a position is a list, you can use a cursor to iterate through each entry in a result set. Cursors can be used in triggers, stored procedures and stored functions, the steps to using a cursor are the following

define the cursor – use the declare statement
open the cursor – use the open statement
retrieve value(s) from the cursor – use the fetch … into statement
close the cursor – use the close statement

Events

An event is a set of SQL commands that can be scheduled to run once or at regular intervals, its very similar to the cron utility in Unix or the Task scheduler in Windows. You can schedule database administrator tasks such as performing backups or logging events, however I do using the O/S’s scheduler.

set global event_scheduler = on;
set global event_scheduler = off;

show global variables like ‘event_scheduler’;