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. 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.

The trigger conditions associated with Sybase IQ events are not the same as Adaptive Server Anywhere or Adaptive Server Enterprise triggers, which execute automatically when a user attempts a specified data modification statement on a specified table.

Examples

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

See the section “Developing event handlers” for more information on the use of the system extended stored procedure xp_sendmail.

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