CREATE EVENT statement

Description

Defines an event and its associated handler for automating predefined actions. Also defines scheduled actions.

Syntax

CREATE EVENT event-nameTYPE event-type
        [ WHERE trigger-conditionAND trigger-condition ], ...]
    | SCHEDULE schedule-spec, … ]
…[ ENABLE | DISABLE ]
…[ ATCONSOLIDATED | 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_conditioncondition-name ) { = | < | >!= | <= | >= } value

schedule-spec:

schedule-name ] { START TIME start-time | BETWEEN start-time AND end-time } [ EVERY periodHOURS | 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

This example instructs 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

This example instructs 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 ;

For more examples, see “Defining trigger conditions for events” in Chapter 6, “Automating Tasks Using Schedules and Events” in the System Administration Guide: Volume 2.

Usage

Events can be used in two main ways:

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.

NoteAlthough statements that return result sets are disallowed in events, you can allow an event to call a stored procedure and insert the procedure results into a temporary table. For details, see “Extraction and events” in Chapter 7, “Moving Data In and Out of Databases,” in System Administration Guide: Volume 1.

CREATE EVENT The 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 The 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 clause The trigger condition determines the condition under which an event is fired. For example, to take an action when the disk containing the transaction log becomes more than 80% full, use the following triggering condition:

...
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 EVENT_CONDITION function [System] in Chapter 4, “SQL Functions” in Reference: Building Blocks, Tables, and Procedures.

SCHEDULE This clause 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.

  1. If the EVERY clause is used, find whether the next scheduled time falls on the current day, and is before the end of the BETWEEN …AND range. If so, that is the next scheduled time.

  2. If the next scheduled time does not fall on the current day, find the next date on which the event is to be executed.

  3. Find the START TIME for that date, or the beginning of the BETWEEN … AND range.

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.


Side effects

Automatic commit.

The actions of an event handler are committed if no error is detected during execution, and rolled back if errors are detected.

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.

See also

ALTER EVENT statement

BEGIN … END statement

COMMENT statement

DROP statement

TRIGGER EVENT statement

Chapter 6, “Automating Tasks Using Schedules and Events” in the System Administration Guide: Volume 2