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;