ALTER EVENT statement

Description

Changes the definition of an event or its associated handler for automating predefined actions. Also alters the definition of scheduled actions.

Syntax

ALTER EVENT event-nameDELETE TYPE | TYPE event-type ]
{    WHEREtrigger-condition | NULL }
    | { ADD | [ MODIFY ] | DELETE } SCHEDULE schedule-spec
}
[ ENABLE | DISABLE ]
[ [ MODIFY ] HANDLER compound-statement | DELETE HANDLER }

Parameters

event-type:

BackupEnd | “Connect” | ConnectFailed | DatabaseStart | DBDiskSpace | “Disconnect” | GlobalAutoincrement | GrowDB | GrowLog | GrowTemp | 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

value | period | day-of-month:

integer

start-time | end-time:

time

start-date:

date

Usage

The ALTER EVENT statement lets you alter an event definition created with CREATE EVENT. Possible uses include the following:

When you alter an event using ALTER EVENT, specify the event name and, optionally, the schedule name.

List event names by querying the system table SYSEVENT. For example:

SELECT event_id, event_name FROM SYS.SYSEVENT

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.

DELETE TYPE clause Removes an association of the event with an event type.

ADD | MODIFY | DELETE SCHEDULE clause Changes the definition of a schedule. Only one schedule can be altered in any one ALTER EVENT statement.

WHERE clause The WHERE NULL option deletes a condition.

For descriptions of most of the parameters, see CREATE EVENT statement.


Side effects

Automatic commit.

Permissions

Must have DBA authority.

See also

BEGIN … END statement

CREATE EVENT statement

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