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-name
[ DELETE TYPE | TYPE event-type ]
{ 	WHERE { trigger-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_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

value | period | day-of-month :: integer

start-time | end-time :: time

start-date :: date

Usage

This statement allows you to alter an event definition created with CREATE EVENT. Possible uses include the following:

You can use ALTER EVENT to change an event handler during development.
You may want to define and test an event handler without a trigger condition or schedule during a development phase, and then add the conditions for execution using ALTER EVENT once the event handler is completed.
You may want to disable an event handler temporarily by disabling the event.

When you alter an event using the ALTER EVENT statement, you specify the event name and, optionally, the schedule name. You can list event names by querying the system table SYSEVENT. For example:

SELECT event_id, event_name FROM SYS.SYSEVENT

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.

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 the CREATE EVENT statement.

Side effects

Automatic commit.

Permissions

Must have DBA authority.