CREATE EVENT

Description

Creates a scheduled event.

Precautions

• Operations related to scheduled events are supported only when sql_compatibility is set to 'B'.
• A user without the sysadmin permission must obtain the permission from the user who has the sysadmin permission to create, modify or delete the scheduled event. The operation permissions of the scheduled event are the same as those of creating scheduled events for the advanced package DBE_SCHEDULER.
• Currently, the interval expression of a scheduled event is compatible with the syntax of floating-point number, for example, interval 0.5 minute. However, the floating-point number is rounded up during calculation. Therefore, you are not advised to use the floating-point number for the interval.
• Scheduled events with the same name are not supported in the same database.
• The statements to be executed in a scheduled event are any SQL statements except security-related operations. However, some statements with restrictions fail to be executed. For example, a database cannot be created by using composite statements.
• The security-related operations are as follows.
  • Use the encryption function.
  • Create and set users and groups.
  • Connect to a database.
  • Encrypt the function.
• The definer fails to be specified for a scheduled event in the following scenarios:
  • The user who operates the scheduled event does not have the sysadmin permission.
  • The current user is inconsistent with the specified definer.
    • An initial user is specified as the definer.
    • An O&M administrator or monitoring administrator is specified as the definer.
    • The parameter enableSeparationOfDuty is set to on to enable the separation of duties.

Syntax

CREATE
    [DEFINER = user]
EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_body;
schedule: {
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]
}
interval:
    quantity {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND |
              YEAR TO MONTH | DAY TO HOUR | DAY TO MINUTE |
              DAY TO SECOND | HOUR TO MINUTE | HOUR TO SECOND | 
              MINUTE TO SECOND}

Parameters

• DEFINER

  Specifies the permission for the scheduled event statement to be executed during execution. By default, the permission of the user who creates the scheduled event is used. When definer is specified, the permission of the specified user is used.

  Only users with the sysadmin permission can specify the definer.

• ON SCHEDULE
  Specifies the time when a scheduled event is executed. A scheduled event can be set to be executed once or multiple times through SCHEDULE.

  • AT timestamp [+ INTERVAL interval] indicates that the scheduled event is executed only once at timestamp [+ INTERVAL interval].
  • EVERY interval indicates that the scheduled event is executed at the interval specified by interval.
    • STARTS timestamp [+ INTERVAL interval] indicates that user can specify the start time for a scheduled event that can be executed repeatedly. That is, the scheduled event is executed since timestamp [+ INTERVAL interval]. If this parameter is left blank, the task is executed from the current time by default.
    • ENDS timestamp [+ INTERVAL interval] indicates that user can specify the end time for a scheduled event that can be executed repeatedly. That is, the scheduled event ends at timestamp [+ INTERVAL interval]. If this parameter is left empty, the default value 3999-12-31 16:00:00 is used.
• INTERVAL

  Specifies the interval. The value of INTERVAL consists of a number and a time unit specified by quantity, for example, 1 YEAR.

• ON COMPLETION [NOT] PRESERVE

  Once a transaction is complete, the scheduled event is deleted from the system catalog immediately by default. You can overwrite the default behavior by setting ON COMPLETION PRESERVE.

• ENABLE | DISABLE | DISABLE ON SLAVE

  The scheduled event is in the ENABLE state by default after it is created. That is, the statement to be executed is executed immediately at the specified time. You can use the keyword DISABLE to change the ENABLE state. The performance of DISABLE ON SLAVE is the same as that of DISABLE.

• COMMENT

  You can add comments to the scheduled event. The comments can be viewed in the GS_JOB_ATTRIBUTE table.

• DO

  Specifies the statement to be executed for a scheduled event.

Example

-- Create a table.
gaussdb=#  CREATE TABLE t_ev(num int);

-- Create a scheduled event that is executed once.
gaussdb=#  CREATE EVENT IF NOT EXISTS event_e1 ON SCHEDULE AT sysdate + interval 5 second + interval 33 minute DISABLE DO insert into t_ev values(0);

-- Create a scheduled event that is executed every minute.
gaussdb=#  CREATE EVENT IF NOT EXISTS event_e1 ON SCHEDULE EVERY 1 minute DO insert into t_ev values(1);