FirebirdSQL logo
 DOMAININDEX 

Restrictions on GTTs

GTTs can be “dressed up” with all the features of ordinary tables (keys, references, indexes, triggers and so on), but there are a few restrictions:

  • GTTs and regular tables cannot reference one another

  • A connection-bound (“PRESERVE ROWS”) GTT cannot reference a transaction-bound (“DELETE ROWS”) GTT

  • Domain constraints cannot reference any GTT

  • The destruction of a GTT instance at the end of its lifecycle does not cause any BEFORE/AFTER delete triggers to fire

Tip

In an existing database, it is not always easy to distinguish a regular table from a GTT, or a transaction-level GTT from a connection-level GTT.Use this query to find out what type of table you are looking at:

select t.rdb$type_name
from rdb$relations r
join rdb$types t on r.rdb$relation_type = t.rdb$type
where t.rdb$field_name = 'RDB$RELATION_TYPE'
and r.rdb$relation_name = 'TABLENAME'

For an overview of the types of all the relations in the database:

select r.rdb$relation_name, t.rdb$type_name
from rdb$relations r
join rdb$types t on r.rdb$relation_type = t.rdb$type
where t.rdb$field_name = 'RDB$RELATION_TYPE'
and coalesce (r.rdb$system_flag, 0) = 0

The RDB$TYPE_NAME field will show PERSISTENT for a regular table, VIEW for a view, GLOBAL_TEMPORARY_PRESERVE for a connection-bound GTT and GLOBAL_TEMPORARY_DELETE for a transaction_bound GTT.

Examples of Global Temporary Tables

  1. Creating a connection-scoped global temporary table.

    CREATE GLOBAL TEMPORARY TABLE MYCONNGTT (
      ID  INTEGER NOT NULL PRIMARY KEY,
      TXT VARCHAR(32),
      TS  TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
    ON COMMIT PRESERVE ROWS;
  2. Creating a transaction-scoped global temporary table that uses a foreign key to reference a connection-scoped global temporary table.The ON COMMIT sub-clause is optional because DELETE ROWS is the default.

    CREATE GLOBAL TEMPORARY TABLE MYTXGTT (
      ID        INTEGER NOT NULL PRIMARY KEY,
      PARENT_ID INTEGER NOT NULL REFERENCES MYCONNGTT(ID),
      TXT       VARCHAR(32),
      TS        TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ON COMMIT DELETE ROWS;