Table of Contents
CREATE EVENT statement is used to create and schedule a new event to be executed once or periodically by Valentina Server.
event_definition_statement : CREATE EVENT [IF NOT EXISTS] event_name FOR [DATABASE] db_name ON SCHEDULE shedule_definition [ ON COMPLETION [ NOT ] PRESERVE ] [ ENABLED | DISABLED ] [ COMMENT literal ] DO sql_statement shedule_definition : AT timestamp [+ INTERVAL interval_value ] | EVERY interval_value [STARTS timestamp] [ENDS timestamp] interval_value : integer_value time_keyword time_keyword : YEAR | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND
IF NOT EXISTS
If an event named event_name already exists in the same schema, no action is taken, and no error results. (However, a warning is generated.)
NOTE: 'IF NOT EXISTS' syntax Valentina have take from mySQL world.
The name of a new event. Must be unique in the scope of Valentina Server.
FOR [DATABASE] db_name
Specifies a database name as known to Valentina Server, for which this event is created.
ON SCHEDULE schedule_definition
Specifies when, how often, and for how long the sql_statement defined for the event repeats. This clause takes one of two forms:
AT timestampclause is used for a one-time event
EVERYclause is used for actions which are to be repeated at a regular interval.
[STARTS timestamp] [ENDS timestamp]
Optional arguments of EVERY clause.
STARTS specifies when event should start repeating. You can use
STARTS. Not using
STARTS is the same as using
ENDS specifies when event should end repeating. You can use
ENDS. Not using
ENDS means that the event continues executing indefinitely.
[ON COMPLETION [NOT] PRESERVE]
Normally, once an event has expired, it is immediately dropped. You can override this behavior by specifying
ON COMPLETION PRESERVE. Using of
ON COMPLETION NOT PRESERVE specifies the default behavior to be explicit.
[ENABLE | DISABLE]
Allows to create a new event in the disabled state. On default a new event is created in the enabled state.
Specifies an optional comment string for this event.
sql_statement to be executed by this event.
- Before execute this command you must switch active database to be masterdb, for this you need use SQL command “USE master;”
timestamp. Timestamp value must include both the date and time. The timestamp must also be in the future — you cannot schedule an event to take place in the past.
- You may use
CURRENT_TIMESTAMPto specify the current date and time. In this case, the event acts as soon as it is created.
intervalvalue consists of two parts, a quantity and a unit of time, and follows the same syntax rules that cover intervals used in the DATE_ADD() function.
- You can also combine intervals. For example,
AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 DAYis equivalent to “three weeks and two days from now”. Each portion of such a clause must begin with + INTERVAL.
This example create event which will do backup of database db1 each midnight.
USE master; CREATE EVENT backup_db1_24 FOR db1 ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP '2007-01-25 23:59:59' DO BACKUP DATABASE
This example increments field f1 in all records of table T1
USE master; CREATE EVENT myevent FOR db1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO UPDATE T1 SET f1 = f1 + 1;