ALTER EVENT statement

Changes the definition of an event or its associated handler for automating predefined actions, or alters the definition of scheduled actions. You can also use this statement to hide the definition of an event handler.

Syntax

Altering an event

ALTER EVENT event-name 
[ AT { CONSOLIDATED | REMOTE | ALL } ]
[ FOR { PRIMARY | ALL} ]
[ { DELETE TYPE 
   | TYPE event-type 
   | WHERE { trigger-condition | NULL }
   | { ADD | ALTER | DELETE } SCHEDULE schedule-spec } ]
[ ENABLE | DISABLE ]
[ [ ALTER ] HANDLER compound-statement | DELETE HANDLER ]

event-type :
BackupEnd 
| Connect 
| ConnectFailed 
| DatabaseStart
| DBDiskSpace
| Deadlock 
| "Disconnect" 
| GlobalAutoincrement 
| GrowDB
| GrowLog 
| GrowTemp
| LogDiskSpace 
| RAISERROR
| ServerIdle 
| TempDiskSpace

trigger-condition :
event_condition( condition-name ) { = | < | > | != | <= | >= } value | @variable-name

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

Hiding the definition of an event handler
```
ALTER EVENT event-name SET HIDDEN
```

Parameters

ALTER EVENT clause
Events do not have owners. If you specify an owner (for example, owner.event-name) the owner portion is ignored.
AT clause
Use this clause to change the specification regarding the databases at which the event is handled.
FOR clause
Use this clause in a database mirroring or read-only scale-out system to restrict the databases at which the event is handled.
DELETE TYPE clause
Use this clause to remove an association of the event with an event type.
ADD | ALTER | DELETE SCHEDULE clause
Use this clause to change the definition of a schedule. Only one schedule can be altered in any one ALTER EVENT statement.
WHERE clause
Use this clause to change the trigger condition under which an event is fired. The WHERE NULL option deletes a condition.

You can specify a variable name for the event_condition value.
START TIME clause
Use this clause to specify the start time and, optionally, the end time, for the event. The start-time and end-time parameters are strings (for example, '12:34:56'). Expressions are not allowed (for example, NOW()).

You can specify a variable name for start-time. If start-time is a NULL string variable, it is ignored.
BETWEEN...AND clause You can specify a variable name for start-time and end-time. If start-time or end-time are NULL string variables, they are ignored.
EVERY clause You can specify a variable name for period. If period is a NULL integer variable, the EVERY clause is ignored.
START DATE clause
Use this clause to specify the start date for the event. The start-date parameter is a string. Expressions are not allowed (for example, TODAY()). If start-date is a NULL string variable, it is ignored.

You can specify a variable name for start-date.
SET HIDDEN clause
Use this clause to hide the definition of an event handler. Specifying the SET HIDDEN clause results in the permanent obfuscation of the event handler definition stored in the action column of the ISYSEVENT system table.

Remarks

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

hiding the definition of an event handler
defining and testing an event handler without a trigger condition or schedule during a development phase, and then adding the conditions for execution using ALTER EVENT once the event handler is completed

Events are not owned. However, when an event is created, a user name is associated with the event (either by explicitly specifying a user name in the CREATE EVENT statement, or implicitly as the creator). The event runs with the privileges of that user name. You cannot use the ALTER EVENT statement to change the user name associated with an event (if you specify a user name, it is ignored). Instead, you must drop and recreate the event, and specify the new user name.

If you need to alter an event, you can disable it while it is running by executing an ALTER EVENT...DISABLE statement. To disable an event in SQL Central, right-click the event and clear the Enabled option. Disabling the event does not interrupt current event handler execution; the event handler continues to execute until completion. When the event handler completes, it is not restarted until you re-enable it. You can alter and then re-enable the definition. To determine what events are running, execute the following statement:

SELECT * 
FROM dbo.sa_conn_info()
WHERE CONNECTION_PROPERTY( 'EventName',Number ) = 'event-name';

An owner specification before event-name is ignored.

Note For required 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

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

Side effects

Automatic commit.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The following example creates then alters an event using variables for the BETWEEN clause.

The following statements create three variables, one for start time, one for end time, and one for interval time:

CREATE VARIABLE @st1 LONG VARCHAR
CREATE VARIABLE @et1 LONG VARCHAR
CREATE VARIABLE @int1 INTEGER
SET @st1 = ' 8:00AM '
SET @et1 = ' 6:00PM '
SET @int1 = 1;

The following statement creates an event that backs up the database's transaction log and uses the created variables for the BETWEEN clause:

CREATE EVENT HourlyLogBackup
   SCHEDULE hourly_log_backup
   BETWEEN @st1 AND @et1
   EVERY @int1 HOURS ON
      ( 'Monday' , 'Tuesday' , 'Wednesday' , 'Thursday' , 'Friday' )
      HANDLER
         BEGIN
            BACKUP DATABASE DIRECTORY 'C:\\database\\backup'
            TRANSACTION LOG ONLY
            TRANSACTION LOG RENAME
         END;

The following statement resets the variable @st1 to 'Now':
```
SET @st1 = ' Now ';
```

The following statement alters the event by changing the start time to 'Now':

ALTER EVENT HourlyLogBackup
      SCHEDULE hourly_log_backup
      BETWEEN @st1 AND @et1
      EVERY @int1 HOURS ON
         ( 'Monday' , 'Tuesday' , 'Wednesday' , 'Thursday' , 'Friday' )
         HANDLER
            BEGIN
               BACKUP DATABASE DIRECTORY 'C:\\database\backup'
                  TRANSACTION LOG ONLY
                  TRANSACTION LOG RENAME
            END;

The following example creates an event that runs incremental backups and then alters the event using variables for the START TIME, EVERY, and START DATE clauses.
1. The following statement creates an event that runs an incremental backup daily at 1 AM.
```
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;
```

The following statements create three variables, one for start time, one for start date, and one for interval time:

CREATE VARIABLE @st2 LONG VARCHAR 
CREATE VARIABLE @sd2 LONG VARCHAR
CREATE VARIABLE @int2 INTEGER
SET @st2 = ' 3:00AM '
SET @sd2 = '2013-01-01'
SET @int2 = 24;

The following statement alters the event IncrementalBackup and uses variables in the START TIME, EVERY, and START DATE clauses:

ALTER EVENT IncrementalBackup
   SCHEDULE
      START TIME @st2 EVERY @int2 HOURS
      START DATE @sd2
         HANDLER
            BEGIN
               BACKUP DATABASE DIRECTORY 'c:\\backup'
               TRANSACTION LOG ONLY
               TRANSACTION LOG RENAME MATCH
            END;