Global Temporary Tables (GTT)

Global temporary tables have persistent metadata, but their contents are transaction-bound (the default) or connection-bound.Every transaction or connection has its own private instance of a GTT, isolated from all the others.Instances are only created if and when the GTT is referenced.They are destroyed when the transaction ends or on disconnect.The metadata of a GTT can be modified or removed using ALTER TABLE and DROP TABLE, respectively.

Syntax

CREATE GLOBAL TEMPORARY TABLE tablename
  (<column_def> [, {<column_def> | <table_constraint>} ...])
  [<gtt_table_attrs>]

<gtt_table_attrs> ::= <gtt_table_attr> [gtt_table_attr> ...]

<gtt_table_attr> ::=
    <sql_security>
  | ON COMMIT {DELETE | PRESERVE} ROWS

Note	Syntax notes `ON COMMIT DELETE ROWS` creates a transaction-level GTT (the default), `ON COMMIT PRESERVE ROWS` a connection-level GTT The `EXTERNAL [FILE]` clause is not allowed in the definition of a global temporary table

GTTs are writable in read-only transactions.The effect is as follows:

Read-only transaction in read-write database: Writable in both ON COMMIT PRESERVE ROWS and ON COMMIT DELETE ROWS
Read-only transaction in read-only database: Writable in ON COMMIT DELETE ROWS only

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.