Event merupakan pembuatan dan menjadwalkan proses yang akan dilakukan. Ini membutuhkan hak istimewa EVENT untuk skema di mana acara akan dibuat (dan mungkin SUPER bergantung pada nilai DEFINER. Proses tidak akan berjalan kecuali Penjadwal diaktifkan.
Untuk mengaktifkan supaya event bisa digunakan adalah :
1. Mengaktifkan
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;
2. Men-Aktifkan
SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;
1. Membuat Event
Format :
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 sql_statement;
schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
Contoh 1 :
CREATE EVENT evtData ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO UPDATE myschema.mytable SET mycol = mycol + 1;
Contoh 2 :
CREATE EVENT evtData
ON SCHEDULE
EVERY 6 HOUR
COMMENT 'A sample comment.'
DO
UPDATE myschema.mytable SET mycol = mycol + 1;
2. Mengubah Event
Format :
ALTER [DEFINER = { user | CURRENT_USER }]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
[DO event_body]
Contoh :
2.1. Mengubah jam
ALTER EVENT evtData
ON SCHEDULE
EVERY 12 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 4 HOUR;
2.2.Mengubah hari
ALTER TABLE evtData
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
TRUNCATE TABLE myschema.mytable;
2.3. Menonaktifkan
ALTER EVENT myevent
DISABLE;
2.4. Menganti nama
ALTER EVENT myevent
RENAME TO yourevent;
2.5. mengarahkan ke database lain
ALTER EVENT olddb.myevent
RENAME TO newdb.myevent;
3. Menghapus Event
Format :
DROP EVENT [IF EXISTS] event_name
Contoh :
DROP EVENT evtData;
