How can we modify an existing MySQL event?


With the help of ALTER EVENT statement, we can modify an existing MySQL event. We can change the various attributes of an event. ALTER EVENT has the following syntax −

   ALTER EVENT event_name
    ON SCHEDULE schedule
ON COMPLETION [NOT] PRESERVE
  RENAME TO new_event_name
    ENABLE | DISABLE
           DO
       event_body

To understand it we are illustrating the example as below −

Example

Suppose we have an event as follows −

mysql> Create event hello ON SCHEDULE EVERY 1 Minute DO INSERT INTO event_messages(message, generated_at) Values ('Alter event testing', NOW());
Query OK, 0 rows affected (0.00 sec)

mysql> select * from event_messages;
+----+---------------------+---------------------+
| ID | MESSAGE             | Generated_at        |
+----+---------------------+---------------------+
|  1 | Without Preserve    | 2017-11-22 20:32:13 |
|  2 | With Preserve       | 2017-11-22 20:35:12 |
|  3 | Alter event testing | 2017-11-22 21:08:37 |
+----+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> ALTER EVENT hello ON SCHEDULE EVERY 2 MINUTE;
Query OK, 0 rows affected (0.00 sec)

The above query will alter the schedule of the event from 1 minute to 2 minutes. And the query below will change the body of the event.

mysql> ALTER EVENT hello DO INSERT INTO event_messages(message,generated_at) VALUES('ALTERED',NOW());
Query OK, 0 rows affected (0.00 sec)

mysql> select * from event_messages;
+----+---------------------+---------------------+
| ID | MESSAGE             | Generated_at        |
+----+---------------------+---------------------+
|  1 | Without Preserve    | 2017-11-22 20:32:13 |
|  2 | With Preserve       | 2017-11-22 20:35:12 |
|  3 | Alter event testing | 2017-11-22 21:08:37 |
|  4 | Alter event testing | 2017-11-22 21:09:15 |
|  5 | ALTERED             | 2017-11-22 21:11:15 |
+----+---------------------+---------------------+
5 rows in set (0.00 sec)

The above result set shows that we got the changed message after 2 minutes.

Samual Sam
Samual Sam

Learning faster. Every day.

Updated on: 22-Jun-2020

315 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements