CREATE EVENT statement

Defines an event and its associated handler for automating predefined actions, and to define scheduled actions.

Syntax

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

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

schedule-name : identifier

day-of-week : string

day-of-month : integer

value : integer

period : integer

start-time : 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 privileges of that creator. This is the same as stored procedure execution. You cannot create events owned by other users.
user-name
Optionally, specify the name of a user in the system; when the event runs, it runs with the privileges of user-name. If this parameter is not specified, the event runs with the privileges of the user who created the event. user-name should not be confused with an owner of the event, however; events do not have owners.
OR REPLACE clause
Specifying OR REPLACE (CREATE OR REPLACE EVENT) creates an event or replaces an event with the same name. If the event already exists, then all comments are preserved when you use the OR REPLACE clause, but all existing attributes of the event are dropped.
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.
- DiskSpace event types
  If the database contains an event handler for one of the DiskSpace types, the database server checks the available space on each device associated with the relevant file every 30 seconds.
  
  In the event the database has more than one dbspace, on separate drives, DBDiskSpace checks each drive and acts depending on the lowest available space.
  
  The LogDiskSpace event type checks the location of the transaction log and any transaction log mirror, and reports based on the least available space.
  
  The TempDiskSpace event type checks the amount of temporary disk space.
  
  If the appropriate event handlers have been defined (DBDiskSpace, LogDiskSpace, or TempDiskSpace), the database server checks the available space on each device associated with a database file every 30 seconds. Similarly, if an event has been defined to handle the system event type ServerIdle, the database server notifies the handler when no requests have been processed during the previous 30 seconds.
  
  You can specify the -fc option when starting the database server to implement a callback function when the database server encounters a file system full condition.
- GlobalAutoincrement event type
  The event fires on each insert when the number of remaining values for a GLOBAL AUTOINCREMENT is less than 1% of the end of its range. A typical action for the handler could be to request a new value for the global_database_id option, based on the table and number of remaining values which are supplied as parameters to this event.
  
  You can use the event_condition function with RemainingValues as an argument for this event type.
- ServerIdle event type
  If the database contains an event handler for the ServerIdle type, the database server checks for server activity every 30 seconds.
- Database mirroring event types
  The MirrorServerDisconnect event fires when a connection from the primary database server to the mirror server or arbiter server is lost, and the MirrorFailover event fires whenever a server takes ownership of the database.
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( 'LogFreePercent' ) < 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.

You can specify a variable name for the event_condition value.
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. You must provide a schedule-name when there is more than one schedule; the schedule-name 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 executes at most once. An attempt to create a non-recurring scheduled event for which the start time has passed generates 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:

START TIME clause
The first scheduled time for each day on which the event is scheduled. The start-time parameter is a string, and cannot be an expression such as NOW(). If a START DATE is specified, the START TIME refers to that date and each subsequent day (if the schedule includes EVERY or ON). If no START DATE is specified, the START TIME is on the current day (unless the time has passed) and each subsequent day (if the schedule includes EVERY or ON). The clause START TIME start-time is equivalent to BETWEEN start-time AND '23:59:59'.

You can specify a variable name for start-time.
BETWEEN...AND clause
A range of times during the day outside which no scheduled times occur. The start-time and end-time parameters are strings, and cannot be expressions such as NOW(). If a START DATE is specified, the scheduled times do not occur until that date.

You can specify a variable name for start-time and end-time.
EVERY clause
An interval between successive scheduled events. Scheduled events occur only after the START TIME for the day, or in the range specified by BETWEEN...AND.

You can specify a variable name for period.
ON clause
A list of days on which the scheduled events occur. The default is every day if EVERY is specified. Days can be specified as days of the week or days of the month.

Days of the week are Mon, Tues, and so on. You may also use the full forms of the day, such as Monday. You must use the full forms of the day names if the language you are using is not English, is not the language requested by the client in the connection string, and is not the language which appears in the database server messages window.

Days of the month are integers from 0 to 31. A value of 0 represents the last day of any month.
START DATE clause
The date on which scheduled events are to start occurring. This value is a string, and cannot be an expression such as TODAY(). The default is the current date.

You can specify a variable name for start-date.

Each time a scheduled event handler is completed, the following actions are taken to calculate the next scheduled time and date for the event:
1. If the EVERY clause is used, find whether the next scheduled time falls on the current day, and is before the end time specified by the BETWEEN...AND clause, if it was specified. 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 and use the START TIME for that date, or the beginning of the BETWEEN...AND range.
ENABLE | DISABLE clause
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
This clause should be used only in the following circumstance: in a SQL Remote setup, use the AT clause against your remote or consolidated databases to restrict the databases at which the event is handled.

If you do not use the AT clause when creating events for SQL Remote, all databases execute the event. When executed on a consolidated database, this statement does not affect remote databases that have already been extracted.
FOR clause
This clause should only be used in the following circumstance: in a database mirroring or read-only scale-out system, use the FOR clause to restrict the databases at which the event is handled.

If you do not use the FOR clause when creating an event for a database in a mirroring or read-only scale-out system, then only the database that is running on the primary server executes the event. The following subclauses are supported:
- FOR PRIMARY
  The event executes only on the server currently acting as the primary server. The default is the PRIMARY sub clause.
  
  When the FOR PRIMARY clause (or the FOR clause is not specified) is used with the DatabaseStart event type, the event executes when a server becomes the primary server for the database.
- FOR ALL
  The event executes on all servers in the system.
  
  When the FOR ALL clause is used with the DatabaseStart event type, the event is executed when any database starts. If in a mirroring system the event did not run when the database started (for example, the database was running before the event was created), then the event can execute during a fail over. For example you start a database mirroring system, you create a DatabaseStart event with the FOR ALL clause, and then you stop the primary server, which causes a fail over. In this example, the event executes on the new primary server. The DatabaseStart event will not execute during subsequent fail overs.
HANDLER clause
Each event has one handler.

Remarks

Events can be used for:

Scheduling actions
The database server executes actions on a timed schedule. You can use this capability to complete scheduled tasks such as backups, validity checks, and queries used to add data to reporting tables.
Event handling actions
The database server executes actions when a predefined event occurs. You can use this capability to complete scheduled tasks such as restrict disk space when a disk fills beyond a specified percentage. Event handler actions are committed if errors are not detected during execution, and rolled back if errors are detected.

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 want to 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 message log.

After each execution of an event handler, a COMMIT occurs if no errors occurred. A ROLLBACK occurs if there was an error.

When event handlers are triggered, the database 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.

Event handlers execute on a separate connection, but the separate connection does not count towards the ten-connection limit of the personal database server.

An owner can be specified but is ignored; events to not have owners. Because events do not have owners, no two events can have the ame name.

Note For parameters that accept variable names, an error is returned if one of the following conditions is true:

The variable does not exist
The contents of the variable are NULL
The variable exceeds the length allowed by the parameter
The data type of the variable does not match that required by the parameter

Privileges

If you are creating a new event, then you must have the MANAGE ANY EVENT or CREATE ANY OBJECT system privilege.

If you are replacing an existing event, then you must have on of the following:

MANAGE ANY EVENT system privilege
ALTER ANY OBJECT system privilege
CREATE ANY OBJECT and DROP ANY OBJECT system privileges

Side effects

Automatic commit.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

Instruct the database server to perform an incremental backup daily at 1:00 A.M.

CREATE EVENT IncrementalBackup
SCHEDULE
 START TIME '1:00 AM' EVERY 24 HOURS
HANDLER
BEGIN
 BACKUP DATABASE DIRECTORY 'c:\\backup'
 TRANSACTION LOG ONLY
 TRANSACTION LOG RENAME MATCH
END;

Instruct the database server to perform an automatic backup of the transaction log only, every hour, Monday to Friday between 8 A.M. and 6 P.M.

CREATE EVENT HourlyLogBackup
SCHEDULE hourly_log_backup
BETWEEN '8:00AM' AND '6: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;

Determine when an event is next scheduled to run:

SELECT DB_EXTENDED_PROPERTY( 'NextScheduleTime', 'HourlyLogBackup');

The following example creates an event that uses a variable for one of the event_condition values, and then creates an event that uses the variable @i1 for the first event_condition value:

CREATE VARIABLE @i1 INTEGER; 
SET @i1 = 10000;

CREATE EVENT LogNotifier
TYPE RAISERROR
WHERE event_condition ( 'ErrorNumber' ) <> @i1 AND event_condition ( 'ErrorNumber' ) <> 7
   HANDLER
      BEGIN
         MESSAGE 'LogNotifier message'
      END;