CREATE EVENT statement

CREATE EVENT [ owner.]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

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.
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.
  
  Disk space event types are not supported on Windows Mobile.
  
  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.

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 a variable or 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'.
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 variables or expressions such as NOW(). If a START DATE is specified, the scheduled times do not occur until that date.
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.
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 a variable or an expression such as TODAY(). The default is the current 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.

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.

You must have the MANAGE ANY EVENT or CREATE ANY OBJECT system privilege.

Automatic commit.

SQL/2008 Vendor extension.

Instruct the database server to perform an automatic backup to tape using the first tape drive, every day at 1 A.M.

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 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');

Create an event that can run on any server in a mirroring or read-only scale-out system.

CREATE EVENT evt1 FOR ALL
HANDLER
BEGIN
MESSAGE CURRENT TIME || ' evt1 active' TO CONSOLE;
END

CREATE EVENT statement

Syntax

Parameters

Remarks

Privileges

Side effects

See also

Standards and compatibility

Examples