Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

21.2.2. ALTER EVENT Syntax

ALTER EVENT event_name
    [ON SCHEDULE schedule]
    [RENAME TO new_event_name]
    [ON COMPLETION [NOT] PRESERVE]
    [COMMENT 'comment']
    [ENABLE | DISABLE]
    [DO sql_statement]

The ALTER EVENT statement is used to change one or more of the characteristics of an existing event without the need to drop and recreate it. The syntax for each of the ON SCHEDULE, ON COMPLETION, COMMENT, ENABLE / DISABLE, and DO clauses is exactly the same as when used with CREATE EVENT. (See Section 21.2.1, “CREATE EVENT Syntax”.)

ALTER EVENT works only with an existing event:

mysql> ALTER EVENT no_such_event 
     >     ON SCHEDULE EVERY '2:3' DAY_HOUR;
ERROR 1517 (HY000): Unknown event 'no_such_event'

In each of the following examples, assume that the event named myevent is defined as shown here:

CREATE EVENT myevent
    ON SCHEDULE EVERY 6 HOUR
    COMMENT 'A sample comment.'
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

The following statement changes the schedule for myevent from once every six hours starting immediately to once every twelve hours, starting four hours from the time the statement is run:

ALTER EVENT myevent
    ON SCHEDULE EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + 4 HOUR;

To disable myevent, use this ALTER EVENT statement:

ALTER EVENT myevent
    DISABLE;

It is possible to change multiple characteristics of an event in a single statement. This example changes the SQL statement executed by myevent to one that deletes all records from mytable; it also changes the schedule for the event such that it executes once, one day after this ALTER EVENT statement is run.

ALTER TABLE myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
    DO
      TRUNCATE TABLE myschema.mytable;

To rename an event, use the ALTER EVENT statement's RENAME TO clause, as shown here:

ALTER EVENT myevent
    RENAME TO yourevent;

The previous statement renames the event myevent to yourevent. (Note: There is no RENAME EVENT statement.)

You can also move an event to a different schema using ALTER EVENT ... RENAME TO ... and schema_name.table_name notation, as shown here:

ALTER EVENT oldschema.myevent
    RENAME TO newschema.myevent;

It is necessary to include only those options in an ALTER EVENT statement which correspond to characteristics that you actually wish to change; options which are omitted retain their existing values. This includes any default values for CREATE EVENT such as ENABLE.


 
 
  Published under the terms of the GNU General Public License Design by Interspire