Altering and Dropping Event in MySQL

Event in MySQL – Part 2

Foreword: In the previous part of the series I explained to you what an event is and how to create it in MySQL.

By: Chrysanthus Date Published: 13 Apr 2016

Introduction

This is part 2 of my series, Event in MySQL. In the previous part of the series I explained to you what an event is and how to create it in MySQL. In this part I explain how to alter an event. I assume you have read the previous part of the series, as this is a continuation.

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;

Dropping an Event
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.

See you in the next part of the series.

Chrys

Broad Network

Related Articles

Altering and Dropping Event in MySQL

Event in MySQL – Part 2

Introduction

Related Links

Comments