CREATE EVENT

The 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 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.

Specifies a database name as known to Valentina Server, for which this event is created.

Starting from v6.2 this can be skipped. In this case, event is created on the “server-level” (not related to any database).

Specifies when, how often, and for how long the sql_statement defined for the event repeats. This clause takes one of two forms:

• AT timestamp clause is used for a one-time event
• EVERY clause is used for actions which are to be repeated at a regular interval.

Optional arguments of EVERY clause.

STARTS specifies when event should start repeating. You can use +INTERVAL with STARTS. Not using STARTS is the same as using STARTS CURRENT_TIMESTAMP.

ENDS specifies when event should end repeating. You can use +INTERVAL with ENDS. Not using ENDS means that the event continues executing indefinitely.

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.

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_TIMESTAMP to specify the current date and time. In this case, the event acts as soon as it is created.

• interval value 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 DAY is 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;