Contents Index CREATE ENCRYPTED FILE statement CREATE EXISTING TABLE statement

ASA SQL Reference
  SQL Statements

CREATE EVENT statement

Description 

Use this statement to define an event and its associated handler for automating predefined actions. Also, to define scheduled actions.

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 ]

event-type :
  BackupEnd   | "Connect"
| ConnectFailed  | DatabaseStart
| DBDiskSpace | "Disconnect"
| GlobalAutoincrement | GrowDB
| GrowLog     | GrowTemp
| 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

Parameters 

CREATE EVENT clause    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.

TYPE clause    You can specify the TYPE clause with an optional WHERE clause; or specify the SCHEDULE.

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. For a description of event-types not listed below, see Choosing a system event.

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

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

A scheduled event is recurring if its definition includes EVERY or ON; if neither of these reserved words is used, the event will execute at most once. An attempt to create a non-recurring scheduled event for which the start time has passed will generate an error. When a non-recurring 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 clause    If you wish to execute events at remote or consolidated databases in a SQL Remote setup, you can use this clause to restrict the databases at which the event is handled. By default, all databases execute the event.

HANDLER clause    Each event has one handler.

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 may wish 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. For more information about event_parameter, see EVENT_PARAMETER function [System].

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.

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.

See also 

BEGIN statement

ALTER EVENT statement

COMMENT statement

DROP statement

TRIGGER EVENT statement

EVENT_PARAMETER function [System]

Standards and compatibility 
Example 

Instruct the database server to carry out an automatic backup to tape using the first tape drive on a Windows NT machine, every day at 1 am.

CREATE EVENT DailyBackup
SCHEDULE daily_backup
START TIME '1:00AM' EVERY 24 HOURS
HANDLER
   BEGIN
      BACKUP DATABASE TO '\\\\.\\tape0'
      ATTENDED OFF
   END

Instruct the database server to carry out an automatic backup of the transaction log only, every hour, Monday to Friday between 8 am and 6 pm.

CREATE EVENT HourlyLogBackup
SCHEDULE hourly_log_backup
BETWEEN '8:00AM' AND '8:00PM'
EVERY 1 HOURS ON
   ('Monday','Tuesday','Wednesday','Thursday','Friday')
HANDLER
   BEGIN
      BACKUP DATABASE DIRECTORY 'c:\\database\\backup'
      TRANSACTION LOG ONLY
      TRANSACTION LOG RENAME
   END

For more examples see Defining trigger conditions for events.

Contents Index CREATE ENCRYPTED FILE statement CREATE EXISTING TABLE statement