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 has taken 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, the 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 that are to be repeated at a regular interval.

Optional arguments of EVERY clause.

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

ENDS specifies when the 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.

It allows for creating 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.

• UP TO v9.7: before executing 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 creates an event that will do a backup of database 'db1' each midnight.

USE master; // not required since v9.7

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; // not required since v9.7

CREATE EVENT myevent FOR db1
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE T1 SET f1 = f1 + 1;

This example creates an event that will do a backup of database db1 each midnight and send notifications by email.

USE master; // not required since v9.7

CREATE EVENT backup_db1_24 FOR db1
    ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP '2007-01-25 23:59:59'
    DO 
        BEGIN 
            BACKUP DATABASE WITH DIAGNOSE;
            SET subject = 'Backup ' + NOW() + ' OK.';            
            __MAIL 
                __FROM 'support@example.com' 
                __TO 'client@example.com'
                __SUBJECT subject
                __BODY ''
                __SMTP 'smtp://localhost' 
                __PORT 25
                __USER 'support@example.com'
                __PASSWORD '123456'
                __SSL true; 
        EXCEPTION           
        WHEN OTHERS THEN 
            SET subject = 'Backup ' + NOW() + ' FAILED.';        
            SET errorMsg = HEX(@@LastErrorNumber) + ' ' + @@LastErrorMessage; 
            __MAIL 
                __FROM 'support@example.com' 
                __TO 'client@example.com'
                __SUBJECT subject
                __BODY errorMsg
                __SMTP 'smtp://localhost' 
                __PORT 25
                __USER 'support@example.com'
                __PASSWORD '123456'
                __SSL true;                        
        END