Defining trigger conditions for events

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.

Examples

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;

Discuss this page in DocCommentXchange.