EVENT_PARAMETER function [System]

Provides context information for event handlers.

Syntax
EVENT_PARAMETER( context-name )
context-name:
  AppInfo
| ConnectionID
| DisconnectReason
| EventName
| Executions
| MirrorServerName
| NumActive
| ScheduleName
| SQLCODE
| TableName
| User
| condition-name
Parameters
  • context-name   One of the preset strings. The strings must be quoted, are case insensitive, and carry the following information:

    • AppInfo   The value of the AppInfo connection property for the connection that caused the event to be triggered. You can see the value of the property from outside of the context of the event by using the following statement:
      SELECT CONNECTION_PROPERTY( 'AppInfo' );

      This parameter is valid for Connect, Disconnect, ConnectFailed, BackupEnd, and RAISERROR events. The AppInfo string contains the computer name and application name of the client connection for embedded SQL, ODBC, OLE DB, ADO.NET, and iAnywhere JDBC driver connections.

    • ConnectionId   The connection ID of the connection that caused the event to be triggered.

    • DisconnectReason   A string indicating the reason the connect was terminated. This parameter is valid only for Disconnect events. Possible results include:

      • abnormal   A disconnect occurred as a result of the client application terminating abnormally prior to disconnecting from the database, or as a result of a communication failure between the client and server computers.

      • connect failed   A connection attempt failed.

      • drop connection   A DROP CONNECTION statement was executed.

      • from client   The client application disconnected.

      • inactive   No requests were received for the period specified by the -ti server option.

      • liveness   No liveness packets were received for the period specified by the -tl server option.

    • EventName   The name of the event that has been triggered.

    • Executions   The number of times the event handler has been executed.

    • MirrorServerName   The name of the mirror or arbiter server that lost its connection to the primary server in a database mirroring system.

    • NumActive   The number of active instances of an event handler. This is useful if you want to limit an event handler so that only one instance executes at any given time.

    • ScheduleName   The name of the schedule which caused an event to be fired. If the event was fired manually using TRIGGER EVENT or as a system event, the result will be an empty string. If the schedule was not assigned a name explicitly when it was created, its name will be the name of the event.

    • SQLCODE   The SQLCODE of the error that occurred during a failed connection. This parameter is valid only for ConnectFailed events.

    • TableName   The name of the table, for use with RemainingValues.

    • User   The user ID for the user that caused the event to be triggered.

    In addition, you can access any of the valid condition-name arguments to the EVENT_CONDITION function from the EVENT_PARAMETER function.

    The following table indicates which context-name values are valid for which system event types.

    Context-name value Valid system event types
    AppInfo BackupEnd, "Connect", ConnectFailed, "Disconnect", "RAISERROR", user events
    ConnectionID BackupEnd, "Connect", "Disconnect", Global Autoincrement, "RAISERROR", user events
    DisconnectReason "Disconnect"
    EventName all
    Executions all
    NumActive all
    SQLCODE ConnectFailed
    TableName GlobalAutoincrement
    User BackupEnd, "Connect", ConnectFailed, "Disconnect", GlobalAutoincrement, "RAISERROR", user events

Remarks

The maximum size of values passed to an event is limited by the maximum page size for the server (-gp server option). Values that are longer are truncated to be slightly less than the maximum page size.

See also
Standards and compatibility
  • SQL/2003   Vendor extension.

Example

The following example shows how to pass a string parameter to an event. The event displays the time it was triggered in the database server messages window.

CREATE EVENT ev_PassedParameter
HANDLER
BEGIN
  MESSAGE 'ev_PassedParameter - was triggered at ' || event_parameter( 'time' );
END;
TRIGGER EVENT ev_PassedParameter( "Time"=string(current timestamp ) );