CREATE EVENT Statement

Syntax

CREATE EVENT event-name
[ TYPE event-type
        [ WHERE trigger-condition [ AND trigger-condition ], ...]
    | SCHEDULE schedule-spec, … ]
…[ ENABLE | DISABLE ]
…[ AT { CONSOLIDATED | REMOTE | ALL } ]
…[ HANDLER
      BEGIN
…
      END ]

Parameters

• event-type: – BackupEnd  |  “Connect”  |  ConnectFailed  |  DatabaseStart  |  DBDiskSpace  |  “Disconnect”  |  GlobalAutoincrement |  GrowDB  |  GrowLog  |  GrowTemp  |  IQMainDBSpaceFree  |  IQTempDBSpaceFree  |  LogDiskSpace  |  “RAISERROR”  |  ServerIdle  |  TempDiskSpace
• trigger-condition: – event_condition( condition-name ) { = | < | > | != | <= | >= } value
• schedule-spec: – [ schedule-name ] { START TIME start-time | BETWEEN start-time AND end-time } [ EVERY period { HOURS | MINUTES | SECONDS } ] [ ON { ( day-of-week, … ) | ( day-of-month, … ) } ] [ START DATE start-date ]
• event-name | schedule-name: – identifier
• day-of-week: – string
• day-of-month | value | period: – integer
• start-time | end-time: – time
• start-date: – date

Examples

• Example 1 – Instruct the database server to carry out an automatic incremental backup daily at 1 a.m.:

  CREATE EVENT IncrementalBackup
  SCHEDULE
  START TIME '1:00AM' EVERY 24 HOURS
  HANDLER
    BEGIN
      BACKUP DATABASE INCREMENTAL
      TO 'backups/daily.incr'
    END

• Example 2 – Instruct the database server to call the system stored procedure sp_iqspaceused every 10 minutes, then store in a table the returned current date and time, the current number of connections to the database, and current information about the use of main and temporary IQ store:

  CREATE TABLE mysummary(dt DATETIME,
    users INT, mainKB UNSIGNED BIGINT,
    mainPC UNSIGNED INT,
    tempKB UNSIGNED BIGINT,
    tempPC UNSIGNED INT) ;

  CREATE EVENT mysummary
    SCHEDULE sched_mysummary
      START TIME '00:01 AM' EVERY 10 MINUTES
    HANDLER
    BEGIN
      DECLARE mt UNSIGNED BIGINT;
      DECLARE mu UNSIGNED BIGINT;
      DECLARE tt UNSIGNED BIGINT;
      DECLARE tu UNSIGNED BIGINT;
      DECLARE conncount UNSIGNED INT;
  
      SET conncount = DB_PROPERTY('ConnCount');
      CALL SP_IQSPACEUSED(mt,mu,tt,tu);
      
  		INSERT INTO mysummary VALUES( NOW(),
      conncount, mu, (mu*100)/mt, tu,
      (tu*100)/tt );
  	END;

• Example 3 – Post a message to the server log when free disk space on the device containing the transaction log file falls below 30 percent, but execute the handler no more than once every 300 seconds.

  CREATE EVENT LowTxnLogDiskSpace
  TYPE DBDiskSpace
  WHERE event_condition( 'DBFreePercent' ) < 30
  AND event_condition( 'Interval' ) >= 300
  HANDLER
  BEGIN
  message 'Disk space for Transaction Log is low.';
  END;

  For more examples, see System Administration Guide: Volume 2 > Automating Tasks Using Schedules and Events > Trigger Conditions for Events.

Usage

An event definition includes two distinct pieces. The trigger condition can be an occurrence, such as a disk filling up beyond a defined threshold. A schedule is a set of times, each of which acts as a trigger condition. When a trigger condition is satisfied, the event handler executes. The event handler includes one or more actions specified inside a compound statement (BEGIN... END).

If no trigger condition or schedule specification is supplied, only an explicit TRIGGER EVENT statement can trigger the event. During development, you might want to develop and test event handlers using TRIGGER EVENT and add the schedule or WHERE clause once testing is complete.

Event errors are logged to the database server console.

When event handlers are triggered, the server makes context information, such as the connection ID that caused the event to be triggered, available to the event handler using the EVENT_PARAMETER function.

CREATE EVENT – event-name is an identifier. An event has a creator, which is the user creating the event, and the event handler executes with the permissions of that creator. This is the same as stored procedure execution. You cannot create events owned by other users.

You can list event names by querying the system table SYSEVENT. For example:

SELECT event_id, event_name FROM SYS.SYSEVENT

TYPE – event-type is one of the listed set of system-defined event types. The event types are case-insensitive. To specify the conditions under which this event-type triggers the event, use the WHERE clause.

...
WHERE event_condition( 'LogDiskSpacePercentFree' ) < 20
...

The argument to the EVENT_CONDITION function must be valid for the event type.

You can use multiple AND conditions to make up the WHERE clause, but you cannot use OR conditions or other conditions.

For information on valid arguments, see Reference: Building Blocks, Tables, and Procedures > SQL Functions > Alphabetical List of Functions > EVENT_CONDITION Function [System].

SCHEDULE – specifies when scheduled actions are to take place. The sequence of times acts as a set of triggering conditions for the associated actions defined in the event handler.

You can create more than one schedule for a given event and its associated handler. This permits complex schedules to be implemented. While it is compulsory to provide a schedule name when there is more than one schedule, it is optional if you provide only a single schedule.

You can list schedule names by querying the system table SYSSCHEDULE. For example:

SELECT event_id, sched_name FROM SYS.SYSSCHEDULE

Each event has a unique event ID. Use the event_id columns of SYSEVENT and SYSSCHEDULE to match the event to the associated schedule.

When a nonrecurring scheduled event has passed, its schedule is deleted, but the event handler is not deleted.

Scheduled event times are calculated when the schedules are created, and again when the event handler completes execution. The next event time is computed by inspecting the schedule or schedules for the event, and finding the next schedule time that is in the future. If an event handler is instructed to run every hour between 9:00 and 5:00, and it takes 65 minutes to execute, it runs at 9:00, 11:00, 1:00, 3:00, and 5:00. If you want execution to overlap, you must create more than one event.

The subclauses of a schedule definition are as follows:

Each time a scheduled event handler is completed, the next scheduled time and date is calculated.

ENABLE | DISABLE – by default, event handlers are enabled. When DISABLE is specified, the event handler does not execute even when the scheduled time or triggering condition occurs. A TRIGGER EVENT statement does not cause a disabled event handler to be executed.

AT – to execute events at remote or consolidated databases in a SQL Remote setup, use this clause to restrict the databases at which the event is handled. By default, all databases execute the event.

HANDLER – each event has one handler. Like the body of a stored procedure, the handler is a compound statement. There are some differences, though: you can use an EXCEPTION clause within the compound statement to handle errors, but not the ON EXCEPTION RESUME clause provided within stored procedures.

See also System Administration Guide: Volume 2 > Automating Tasks Using Schedules and Events.

Standards

Permissions

Must have DBA authority.

Event handlers execute on a separate connection, with the permissions of the event owner. To execute with permissions other than DBA, you can call a procedure from within the event handler: the procedure executes with the permissions of its owner. The separate connection does not count towards the ten-connection limit of the personal database server.