Each event definition has a system event associated with it. It also has one or more trigger conditions. The event handler is triggered when the trigger conditions for the system event are satisfied.
The trigger conditions are included in the WHERE clause of the CREATE EVENT statement, and can be combined using the AND keyword. Each trigger condition is of the following form:
event_condition( condition-name ) comparison-operator value
The condition-name argument is one of a set of preset strings, which are appropriate for different event types. For example, you can use DBSize (the database file size in megabytes) to build a trigger condition suitable for the GrowDB system event. The database server does not check that the condition-name matches the event type: it is your responsibility to ensure that the condition is meaningful in the context of the event type.
Limit the transaction log size to 10 MB:
CREATE EVENT LogLimit TYPE GrowLog WHERE event_condition( 'LogSize' ) > 10 HANDLER BEGIN IF event_parameter( 'NumActive' ) = 1 THEN BACKUP DATABASE DIRECTORY 'c:\\logs' TRANSACTION LOG ONLY TRANSACTION LOG RENAME MATCH; END IF; END;
Notify an administrator when free disk space on the device containing the database file falls below 10%, but do not execute the handler more than once every five minutes (300 seconds):
CREATE EVENT LowDBSpace TYPE DBDiskSpace WHERE event_condition( 'DBFreePercent' ) < 10 AND event_condition( 'Interval' ) >= 300 HANDLER BEGIN CALL xp_sendmail( recipient='DBAdmin', subject='Low disk space', "message"='Database free disk space ' || event_parameter( 'DBFreeSpace' ) ); END;
Notify an administrator of a possible attempt to break into the database:
CREATE EVENT SecurityCheck TYPE ConnectFailed HANDLER BEGIN DECLARE num_failures INT; DECLARE mins INT; INSERT INTO FailedConnections( log_time ) VALUES ( current timestamp ); SELECT count( * ) INTO num_failures FROM FailedConnections WHERE log_time >= DATEADD( minute, -5, current timestamp ); IF( num_failures >= 3 ) THEN SELECT DATEDIFF( minute, last_notification, current timestamp ) INTO mins FROM Notification; IF( mins > 30 ) THEN UPDATE Notification SET last_notification = current timestamp; CALL xp_sendmail( recipient='DBAdmin', subject='Security Check', "message"= 'over 3 failed connections in last 5 minutes' ) END IF END IF END;
Run a process when the server has been idle for ten minutes. Do not execute more frequently than once per hour:
CREATE EVENT Soak TYPE ServerIdle WHERE event_condition( 'IdleTime' ) >= 600 AND event_condition( 'Interval' ) >= 3600 HANDLER BEGIN MESSAGE ' Insert your code here ... ' END;