Altering and Dropping Event in MySQL
Event in MySQL – Part 2
Forward: In this part of the series I explain how to alter an event.
By: Chrysanthus Date Published: 4 Sep 2012
Introduction
The Alter Event Syntax Simplified
A simplified form of the Alter Event Syntax is:
ALTER
EVENT event_name
[ON SCHEDULE schedule]
[COMMENT 'comment']
[DO event_body];
The clauses here are optional. However, they are the same clauses in the Create Event Syntax. So to alter an existing event, you simply replace the content of the clause that is in the Create Event statement. You can also add a new clause (e.g. comment). Just after an event is altered, its effect restarts from that time onward.
Illustration
In the previous part of the series we saw a create event example. This is the example:
CREATE EVENT myEvent
ON SCHEDULE
EVERY 24 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 5 HOUR
DO
UPDATE myTable SET myColumn = myColumn + 1 WHERE ID = 3;
Assume that this event already exists. You can alter the event, changing only the ON SCHEDULE clause as follows:
CREATE EVENT myEvent
ON SCHEDULE
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 HOUR
Note: You do not have to re-type the other clauses; they still remain. The following example changes only the DO clause (read it and compare with the create event statement above):
CREATE EVENT myEvent
DO
UPDATE myTable SET myColumn = myColumn + 2 WHERE ID = 4;
To drop an event means to delete the event. The syntax to drop an event is:
DROP EVENT event_name
To drop the above event, you would type:
DROP EVENT myEvent;
Now you know how to create, alter and drop an event in MySQL. That is good. However, you still may not be able to do these things for the following two reasons:
- What is called the Event Scheduler has to be ON.
- You need to have the right (privilege) to do these things.
In the next part of the series, I look at the privilege that you need to have in order to do these things. In the part that follows I explain the Event Scheduler.
This series is the last of the series I have for the My SQL course (volume). I hope you are appreciating it.
See you in the next part of the series.
Chrys