ADS 10 Tip #5 – System Variables

Several new system variables have been added to the SQL engine allowing developers to get more metadata within SQL scripts. System variables can be divided into four categories: Server Level, Connection Level, Statement Level and Error handling.

Many system variables such as __errorclass, ::conn.name and ::stmt.trigrecno were available in version 9. With version 10 several new system variables have been added.

Server Variables
::server.OldestActiveTxn	Oldest active transaction as a timestamp. Returns NULL if no transactions are active on the server.

Connection Variables
::conn.TransactionCount	Nesting level of the current transaction 0 if there is no active transaction on the connection.
::conn.OperationCount	The number of operations performed by the server for the current connection

Statement Variables
::stmt.TrigRowID	Returns the ROWID of the record that fired the trigger
::stmt.TrigName	Name of the trigger being fired
::stmt.TrigTableName	Name of the table that fired the trigger
::stmt.TrigEventType	Type of event that caused the trigger to be fired: INSERT, UPDATE or DELETE
::stmt.TrigType	Type of trigger: BEFORE, INSTEAD OF, AFTER or CONFLICT

All of these system variables can be accessed within an SQL statement. See the examples below.

// If we are in a nested transaction rollback to a savepoint
IF ::conn.TransactionCount > 1 THEN
  ROLLBACK TO SAVEPOINT MySavepoint;
ELSE
  ROLLBACK;
END IF;

/* A generic INSERT that writes to an audit table
 * System Variables allow the same script to be used 
 * as a trigger for any table */
INSERT INTO AuditTbl ( TableName, Operation, UserID, DateTime )
  VALUES ( ::stmt.TrigTableName, ::stmt.TrigEventType, USER(), NOW() )