Data Definition Language (DDL)

(v.2.1) A database trigger is a PSQL module that is executed when a connection or transaction event occurs. The events and the timings of their triggers are as follows.-

Syntax

<database-trigger> ::=
  {CREATE | RECREATE | CREATE OR ALTER}
    TRIGGER <name>
    [ACTIVE | INACTIVE]
    ON <event>
    [POSITION <n>]
  AS
    BEGIN
      ...
    END

<event> ::=
  CONNECT
    | DISCONNECT
    | TRANSACTION START
    | TRANSACTION COMMIT
    | TRANSACTION ROLLBACK
      

Rules and Restrictions

  gbak -nodbtriggers
  isql -nodbtriggers
  nbackup -T
        

(v.2.1) Global temporary tables (GTTs) are tables that are stored in the system catalogue with permanent metadata, but with temporary data. Data from different connections (or transactions, depending on the scope) are isolated from each other, but the metadata of the GTT are shared among all connections and transactions.

There are two kinds of GTT:

CREATE GLOBAL TEMPORARY TABLE
  ...
  [ON COMMIT <DELETE | PRESERVE> ROWS]
	

A couple of enhancements were made to view definitions in v.2.1.-

CREATE VIEW V_TEST AS
  SELECT ID,
         COL1 AS CODE,
         COL2 AS NAME
  FROM TAB;
        

Feature request CORE-711

(v.2.1) Alternative syntax is now available for CREATE TRIGGER that complies with SQL2003.

Syntax Patterns

Existing Form

create trigger t1
  FOR atable
  [active] before insert or update
as
  begin
    ...
  end
      

SQL2003 Form

create trigger t2 
  [active] before insert or update 
  ON atable
as 
  begin
    ...
  end
      

Note the different positions of the clause identifying the table and the different keywords pointing to the table identifier (existing: FOR; SQL2003: ON).

Both syntaxes are valid and are available also for all CREATE TRIGGER, RECREATE TRIGGER and CREATE OR ALTER TRIGGER statements.

Feature request CORE-1386

(v.2.1) SQL-compliant alternative syntax GENERATED ALWAYS AS was implemented for defining a computed field in CREATE/ALTER TABLE.

Syntax Pattern

<column name> [<type>] GENERATED ALWAYS AS ( <expr> )
      

It is fully equivalent semantically with the legacy form:

<column name> [<type>] COMPUTED [BY] ( <expr> )
      

Example

CREATE TABLE T (PK INT, EXPR GENERATED ALWAYS AS (PK + 1))
      

SEQUENCE has been introduced as a synonym for GENERATOR, in accordance with SQL-99. SEQUENCE is a syntax term described in the SQL specification, whereas GENERATOR is a legacy InterBase syntax term. Use of the standard SEQUENCE syntax in your applications is recommended.

A sequence generator is a mechanism for generating successive exact numeric values, one at a time. A sequence generator is a named schema object. In dialect 3 it is a BIGINT, in dialect 1 it is an INTEGER.

Syntax patterns

    CREATE { SEQUENCE | GENERATOR } <name>
    DROP { SEQUENCE | GENERATOR } <name>
    SET GENERATOR <name> TO <start_value>
    ALTER SEQUENCE <name> RESTART WITH <start_value>
    GEN_ID (<name>, <increment_value>)
    NEXT VALUE FOR <name>
      

Examples

1.

   CREATE SEQUENCE S_EMPLOYEE;
      

2.

   ALTER SEQUENCE S_EMPLOYEE RESTART WITH 0;
      

See also the notes about NEXT VALUE FOR.

SYSDBA, the database creator or the owner of an object can grant rights on that object to other users. However, those rights can be made inheritable, too. By using WITH GRANT OPTION, the grantor gives the grantee the right to become a grantor of the same rights in turn. This ability can be removed by the original grantor with REVOKE GRANT OPTION FROM user.

However, there's a second form that involves roles. Instead of specifying the same rights for many users (soon it becomes a maintenance nightmare) you can create a role, assign a package of rights to that role and then grant the role to one or more users. Any change to the role's rights affect all those users.

By using WITH ADMIN OPTION, the grantor (typically the role creator) gives the grantee the right to become a grantor of the same role in turn. Until FB v2, this ability couldn't be removed unless the original grantor fiddled with system tables directly. Now, the ability to grant the role can be removed by the original grantor with REVOKE ADMIN OPTION FROM user.

Domains allow their defaults to be changed or dropped. It seems natural that table fields can be manipulated the same way without going directly to the system tables.

Syntax Pattern

  ALTER TABLE t ALTER [COLUMN] c SET DEFAULT default_value;
  ALTER TABLE t ALTER [COLUMN] c DROP DEFAULT;
      

The DDL statements RECREATE EXCEPTION and CREATE OR ALTER EXCEPTION (feature request SF #1167973) have been implemented, allowing either creating, recreating or altering a custom exception, depending on whether it already exists.

ALTER EXTERNAL FUNCTION has been implemented, to enable the entry_point or the module_name to be changed when the UDF declaration cannot be dropped due to existing dependencies.

The COMMENT statement has been implemented for setting metadata descriptions.

Syntax Pattern

  COMMENT ON DATABASE IS {'txt'|NULL};
  COMMENT ON <basic_type> name IS {'txt'|NULL};
  COMMENT ON COLUMN tblviewname.fieldname IS {'txt'|NULL};
  COMMENT ON PARAMETER procname.parname IS {'txt'|NULL};
        

An empty literal string '' will act as NULL since the internal code (DYN in this case) works this way with blobs.

  <basic_type>:
     DOMAIN
     TABLE
     VIEW
     PROCEDURE
     TRIGGER
     EXTERNAL FUNCTION
     FILTER
     EXCEPTION
     GENERATOR
     SEQUENCE
     INDEX
     ROLE
     CHARACTER SET
     COLLATION
     SECURITY CLASS1
      

¹not implemented, because this type is hidden.

FIRST/SKIP and ROWS syntaxes and PLAN and ORDER BY clauses can now be used in view specifications.

From Firebird 2.0 onward, views are treated as fully-featured SELECT expressions. Consequently, the clauses FIRST/SKIP, ROWS, UNION, ORDER BY and PLAN are now allowed in views and work as expected.

Syntax

For syntax details, refer to Select Statement & Expression Syntax in the chapter about DML.

The DDL statement RECREATE TRIGGER statement is now available in DDL. Semantics are the same as for other RECREATE statements.

The following changes will affect usage or existing, pre-Firebird 2 workarounds in existing applications or databases to some degree.

   declare filter <name> input_type <number> output_type <number>
     entry_point <function_in_library> module_name <library_name>;
      

   declare filter <name> input_type <mnemonic> output_type <mnemonic>
     entry_point <function_in_library> module_name <library_name>;
      

   select RDB$TYPE, RDB$TYPE_NAME, RDB$SYSTEM_FLAG
     from rdb$types
     where rdb$field_name = 'RDB$FIELD_SUB_TYPE';

   RDB$TYPE   RDB$TYPE_NAME                RDB$SYSTEM_FLAG
   ========= ============================ =================

           0 BINARY                                     1
           1 TEXT                                       1
           2 BLR                                        1
           3 ACL                                        1
           4 RANGES                                     1
           5 SUMMARY                                    1
           6 FORMAT                                     1
           7 TRANSACTION_DESCRIPTION                    1
           8 EXTERNAL_FILE_DESCRIPTION                  1
      

    declare filter pesh input_type 0 output_type 3
      entry_point 'f' module_name 'p';
      

    declare filter pesh input_type binary output_type acl
      entry_point 'f' module_name 'p';
      

  SQL> insert into rdb$types
  CON> values('RDB$FIELD_SUB_TYPE', -100, 'XDR', 'test type', 0);
  SQL> commit;
  SQL> declare filter pesh2 input_type xdr output_type text
  CON> entry_point 'p2' module_name 'p';
  SQL> show filter pesh2;
  BLOB Filter: PESH2
          Input subtype: -100 Output subtype: 1
          Filter library is p
          Entry point is p2