Chapter 9 SQL Statements
439
1 If the EVERY clause is used, find whether the next scheduled time falls
on the current day, and is before the end of the BETWEEN … AND
range. 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.
3 Find the START TIME for that date, or the beginning of the BETWEEN
… AND range.
ENABLE | DISABLE 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 If you wish to execute events at remote or consolidated
databases in a SQL Remote setup, you can use this clause to restrict the
databases at which the event is handled. By default, all databases execute the
event.
HANDLER clause Each event has one handler. Like the body of a stored
procedure or trigger, the handler is a compound statement. There are some
differences, though: you can use an EXCEPTION clause within the
compound statement to handle errors, but not the ON EXCEPTION
RESUME clause provided within stored procedures.
♦
SQL/92 Vendor extension.
♦
Sybase Not supported by Adaptive Server Enterprise.
♦ Instruct the database server to carry out an automatic backup to tape
using the first tape drive on a Windows NT machine, every day at 1 am.
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 carry out an automatic backup of the
transaction log only, every hour, Monday to Friday between 8 am and 6
pm.
Standards and
compatibility
Examples
To Next Page
Loading...