Navigation:  »No topics above this level«

Database Triggers

Advantage Concepts

Return to chapter overview

Trigger Event Types

Database triggers are a type of trigger that is defined at the database object level (as opposed to being associated with a table). Database triggers are fired in response to certain events.  Currently, the following combinations of trigger type and event types are supported:

 

Event Type

Supported Trigger Types

Description

OPEN_TABLE

BEFORE, AFTER

Fires when low level open table requests are made. A BEFORE trigger fires before the open attempt is made, so it can fire for open requests on non-existent tables. An AFTER trigger fires after successfully opening the table. A trigger that throws an exception (or inserts a row into the __error table) will cause the open table request to be cancelled and an error returned. Note that when table caching is in effect (the default with SQL statements) not all opens for the user will necessarily be logged. For example, if a the SQL "SELECT * FROM mytable" is run 5 times by a user, it is possible that only the first open request will result in firing the trigger.

CLOSE_TABLE

BEFORE, AFTER

Fires when a low level close request is made. Note that the close request does not necessarily mean the file is physically being closed. Advantage opens each table physically only one time and references counts the actual opens. CLOSE_TABLE triggers cannot be used to cancel the close request. Errors returned from the trigger are logged to the Advantage error log (e.g., ads_err.adt), but the error is then ignored by the server.

CONNECT

AFTER

Fires after a successful connection to a data dictionary. If the trigger throws an exception (or inserts a row into the __error table), the connection will be disallowed except for ADSSYS. When ADSSYS connects, the trigger will fire, but errors are ignored and the connection is allowed. If the trigger does throw an exception or return an error, the native error that is returned to the client will be 7109.

DISCONNECT

BEFORE

Fires at the beginning of the disconnect process. DISCONNECT triggers cannot be used to cancel the disconnect request. If a trigger returns an error, the error is logged, but it is ignored by the disconnect logic.

 

Note: Database triggers are stored in the data dictionary as trigger objects (the same as table triggers). If you create a database trigger in a dictionary and then use that dictionary with older versions of Advantage (pre v12.0), it is possible that some system tables won't be retrieved if requested. And, of course, the database triggers will not be fired.

Example

As an example, to define a trigger that logs when a certain table is opened and closed, you might create triggers such as the following. For syntax information and additional examples, see the Create Trigger Syntax.

 

CREATE TRIGGER LogOpenTable ON DATABASE AFTER OPEN_TABLE

BEGIN

  IF __info.tablename = 'Salaries' THEN

     INSERT INTO logopenclose VALUES (now(), __info.UserName, __info.ClientName,

          __info.FullPath, __info.TrigEvent );

  END IF;

END;

 

 

CREATE TRIGGER LogOpenTable ON DATABASE BEFORE CLOSE_TABLE

BEGIN

  IF __info.tablename = 'Salaries' THEN

     INSERT INTO logopenclose VALUES (now(), __info.UserName, __info.ClientName,

          __info.FullPath, __info.TrigEvent );

  END IF;

END;

 

 

__INFO Table

The __info table contains read-only information about the event. For OPEN_TABLE and CLOSE_TABLE event types, the available information in the __info table includes the following:

 

Field Name

Field Type

Description

UserID

Integer

The internally maintained user ID that is associated with each individual connection. A possible use for this value might be to associate different individual events with each other.

TrigType

CiChar(10)

The trigger type (e.g., BEFORE, AFTER).

TrigEvent

CiChar(15)

The event type (e.g., OPEN_TABLE, CLOSE_TABLE)

TableName

CiChar(256)

The base name of the table. For temporary tables this is the temporary name used to create the table (without the # hash symbol prefix).

FullPath

CiChar(256)

The physical file path of the table.

UserName

CiChar(50)

The database login name.

ClientName

CiChar(50)

The client workstation name if available.

OpenCount

Integer

The number of open instances of this table. This is only available for AFTER OPEN_TABLE and BEFORE CLOSE_TABLE tiggers. It is empty/null for other triggers since the information is not available at that time.

IsShared

Logical

Indicates if the table is opened shared (false indicates exclusive open).

IsFree

Logical

Indicates if the table open is as a free table (not listed in the dictionary).

LockType

Integer

This value indicates the locking type requested (0 = ADS_COMPATIBLE_LOCKING, 1 = ADS_PROPRIETARY_LOCKING).

TableType

Integer

This indicates the table type requsted (1 = ADS_NTX, 2 = ADS_CDX, 3 = ADS_ADT, 4 = ADS_VFP).

IsDirect

Logical

A value of TRUE Indicates the table is opened directly from the client (e.g., a navigational type of open) and FALSE indicates it was opened in response to a request on the server (e.g., part of an SQL statement). This is only available for OPEN_TABLE events.

IsTemp

Logical

Indicates if the it is a temporary table.

 

The __info table for CONNECT and DISCONNECT events contains the following fields:

 

Field Name

Field Type

Description

UserID

Integer

The internally maintained user ID that is associated with each individual connection. A possible use for this value might be to associate different individual events with each other.

TrigType

CiChar(10)

The trigger type (e.g., BEFORE, AFTER).

TrigEvent

CiChar(15)

The event type (e.g., OPEN_TABLE, CLOSE_TABLE)

UserName

CiChar(50)

The database login name

ClientName

CiChar(50)

The client workstation name if available.

LoginName

CiChar(50)

The client workstation login name if available.

ConnPath

CiChar(256)

The database connection path.

Address

CiChar(30)

The client network address if available.

AppID

CiChar(70)

The application identifier (ApplicationID).

OpCount

Integer

The number of operations recorded by the user. This is the number of low level operations that the server tracks (not necessarily individual client requests). For example, a single SQL statement can result in many low level operations.

StmtCount

Integer

The number of SQL statements executed by this connection.

CommType

CiChar(10)

Indicates the communications type. These include TCP_IP, UDP_IP, TLS, or SMC (shared memory comm). For local server usage, this will have the value LOCAL. See sp_GetSecurityInfo.

EncrType

CiChar(10)

Indicates the type of encryption in use by the connection if applicable. Values included AES128, AES256, RC4. See sp_GetSecurityInfo.

TLSCipher

CiChar(20)

For TLS connections, this shows the cipher in use. See sp_GetSecurityInfo.

TLSVersion

CiChar(20)

For TLS connections, this shows the version information. See sp_GetSecurityInfo.

 

Table Type in Database Triggers

The table type that is in effect when triggers run depend on the current settings in effect (e.g., the settings in use by the currently running SQL statement). For CONNECT triggers, no SQL statement will be active yet, so the table type will default to ADT. This should not typically be a problem unless you are accessing a free table (or creating a free table in the trigger). The table type in that case will be dependent on the most recent SQL setting. Therefore, it is advisable to use database tables (as opposed to free tables) inside triggers to avoid the possibility of unexpected table type dependencies.