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 :
2. Setting the time :
3. Original Table :
4. Table after Event :
Best Open Source Business Intelligence Software Helical Insight is Here
