ASA Database Administration Guide
  Automating Tasks Using Schedules and Events
    Understanding events

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
     backup database
     directory 'c:\\logs'
     transaction log only
     transaction log rename match
  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