Understanding system events

SQL Anywhere tracks several system events. Each system event provides a hook on which you can hang a set of actions. The database server tracks the events for you, and executes the actions (as defined in the event handler) when the system event satisfies a provided trigger condition.

For more information about trigger conditions, see Defining trigger conditions for events.

By defining event handlers to execute when a chosen system event occurs and satisfies a trigger condition that you define, you can improve the security and safety of your data, and help ease administration. The actions of an event handler are committed if no error is detected during execution, and rolled back if errors are detected.

The available system events include the following:

BackupEnd You can use the BackupEnd event type to take action at the end of a backup.
Connection events When a connection is made (Connect) or when a connection attempt fails (ConnectFailed). You may want to use these events for security purposes. As an alternative to a connect event handler, you may want to consider using a login procedure. See login_procedure option.
DatabaseStart You can use the DatabaseStart event type to take action when a database is started.
Deadlock You can use the Deadlock event to take action when a deadlock occurs. The event handler can use the sa_report_deadlocks procedure to obtain information about the conditions that led to the deadlock. When using the Deadlock event, you should configure the database server to capture deadlock information by setting the log_deadlocks option to On, and by enabling the RememberLastStatement feature using sa_server_option or the -zl server option.
Deadlock events fire for connection deadlocks and thread deadlocks. A deadlock event provides no information beyond what is available via the sa_report_deadlocks system procedure. However, using this event allows you to act on the deadlock in a timely manner. A quick response may be important since the amount of deadlock-related information the database server maintains is limited. See:
Disconnect You can use the Disconnect event to take action when a user or application disconnects.
Free disk space Tracks the available disk space on the device holding the database file (DBDiskSpace), the log file (LogDiskSpace), or temporary file (TempDiskSpace). This system event is not available on Windows Mobile.
You may want to use disk space events to alert administrators of a disk space shortage.

You can specify the -fc option when starting the database server to implement a callback function when the database server encounters a file system full condition. See -fc dbeng12/dbsrv12 server option.
File size The file reaches a specified size. This can be used for the database file (GrowDB), the transaction log (GrowLog), or the temporary file (GrowTemp).
You may want to use file size events to track unusual actions on the database, or monitor bulk operations.
GlobalAutoincrement When the number of remaining values for a column defined with GLOBAL AUTOINCREMENT is less than one percent of its range, the GlobalAutoincrement event fires. This can be used to request a new value for the global_database_id option based on the table and number of remaining values that are supplied as parameters to this event. To get the remaining values for the table within the event, use the EVENT_PARAMETER function with the RemainingValues and TableName parameters. RemainingValues returns the number of remaining values that can be generated for the column, while TableName returns the table containing the GLOBAL AUTOINCREMENT column that is near the end of its range. See EVENT_PARAMETER function [System].
RAISERROR error When a RAISERROR statement is executed, you can use the RAISERROR event type to take actions. The error number used in the RAISERROR statement can be determined within the event handler using the EVENT_CONDITION function (for example, EVENT_CONDITION( 'ErrorNumber' )).
Idle time The database server has been idle for a specified time (ServerIdle). You may want to use this event type to perform routine maintenance operations at quiet times.
Database mirroring When the connection from the primary server to a mirror server or arbiter server is lost, the MirrorServerDisconnect event fires. To get the name of the server whose connection was lost, use the EVENT_PARAMETER function with the MirrorServerName parameter. See EVENT_PARAMETER function [System].
The MirrorFailover event fires whenever a server takes ownership of the database. For example, it fires when a server first starts and determines that it should own the database. It also fires when a server previously acting as the mirror determines that the primary server has gone down and, after consulting with the arbiter, determines that it should take ownership.

Events are not fired on a server that is currently acting as the mirror server since its copy of the database is still being started. As well, mirroring events cannot be defined to execute on an arbiter, since events only run in the context of the database in which they are defined, and the arbiter does not use a copy of the database being mirrored. See Database mirroring system events.

Defining trigger conditions for events

Discuss this page in DocCommentXchange.