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() )
No comments:
Post a Comment