Friday, June 4, 2010

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

/* 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() )

No comments: