SQL Event Scheduling

 

Event is responsible for making sure that a specified routine (section of query code) is executed at regular intervals that is specified by the user.

An example of use of an Event would be as follows :
A retail store owner wants to see his remaining stocks in his store, in the morning before he opens the store. At such a time, an Event can be created to run at 8:00am, everyday, that will update his database based on the available material.
Under this condition, an Event will be specified which will empty (TRUNCATE) the “Final_Stocks” table and refill it with data from all other tables in the database which are holding the information of remaining stock of goods.

Syntax :
CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT ‘comment’]
DO event_body;

  • event_name : This is the name of the Event that is being created.
  • schedule : The schedule section deals with the starting, ending, and time intervals of repeating the Event.

 

The schedule section has the following parts :

AT timestamp [+ INTERVAL interval] …
| EVERY interval [STARTS timestamp [+ INTERVAL interval] …]
[ENDS timestamp [+ INTERVAL interval] …]

  • Interval : The interval section specifies the time interval after the previous execution the event needs to wait before being triggered again. The interval should have the following syntax :

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
Where quantity specifies the duration of unit time (specified in the next section), eg: 3 HOUR.

  • Timestamp : The timestamp uses the current date – time timestamp to specify one of the parameters while scheduling the Event.
  • event_body : This is where the main body of the Event is specified. This section holds the working of the event, what all it should do.

 

Example:
1. Creating an Event :

1

2. Setting the time :

2

3. Original Table :

3

4. Table after Event :

4

Leave a Reply