Exceptions are user-defined named error messages, written specifically for a database and stored in that database for use in stored procedures and triggers.

If it is ascertained in a trigger that the value in a table is incorrect, the exception is fired. This leads to a rollback of the total transaction that the client application is attempting to commit. Exceptions can be interleaved.

They can be shared among the different modules of an application, and even among different applications sharing a database. They provide a simple way to standardize the handling of preprogrammed input errors. Exceptions are typically used to implement program logic, for example, you do not wish a user to sell an item in stock, which has already been reserved by another user for their customer.

Exceptions are database objects and are part of the database's metadata, and can be created, modified and dropped as all other Firebird/InterBase® objects in the IBExpert DB Explorer.

The maximum size of an exception message was raised in Firebird 2.0 from 78 to 1021 bytes.

back to top of page

A new exception can be created in a connected database either by using the menu item Database / New Exception, the respective icon in the New Database Object toolbar, or using the DB Explorer right-click menu (or key combination [Ctrl + N]), when the exception heading of the relevant connected database is highlighted. A New Exception dialog appears, with its own toolbar:

Exception messages can be displayed as Unicode (UTF8) and UTF8 is also supported for descriptions and the DDL page. Exception messages are converted to URF8 to avoid the malformed string error.

Alternatively, a new exception can be created directly in the IBExpert SQL Editor, using the following statement:

CREATE EXCEPTION <Exception_Name>
"Exception_Text";

The Exception Editor can be opened directly from the DB Explorer by double-clicking on any existing exception name. It can also be started directly from any procedure or trigger containing an exception, simply by double-clicking on the exception name in the SQL text on the Procedure Editor's Edit page, or the Trigger Editor's Triggers page.

The new exception name can be added to the list displaying all exceptions for the active database, and the exception text message entered. Please be careful when using special characters! Especially when using older versions of InterBase®, it is preferable to abstain from using any special characters. With the newer versions, there should not be any problems, provided the correct character set has been specified. The exception ID is automatically assigned by the database, when the exception is committed.

After creating the exception, it then needs to be incorporated into a stored procedure or a trigger, to determine under what conditions and when the exception is to appear. Please refer to Raising an Exception for details.

Please refer to Table Editor / Dependencies.

Please refer to Table Editor / DDL.

Please refer to Table Editor / Comparison.

Please refer to Table Editor / To-Do.

back to top of page

The EXCEPTION statement is used to notify a calling application of an exception. The calling application can be a trigger, a stored procedure, or another program. To raise an exception in a trigger or stored procedure use the EXCEPTION keyword:

EXCEPTION <Exception_Name>;

When an exception is raised, the following takes place:

1. The exception terminates the trigger or procedure.
2. Any statements in the trigger or stored procedure that follow the EXCEPTION statement are not executed. In the case of a BEFORE trigger the update that fired the trigger is aborted.
3. The trigger or procedure returns an error message to the calling application.

Exceptions may be trapped and handled with a WHEN statement in a stored procedure or trigger.

An example of an exception raised in a procedure can be found in the EMPLOYEE database. The exception REASSIGN_SALES was first created:

and then incorporated into the DELETE_EMPLOYEE procedure:

back to top of page

Exceptions can be altered directly in the Exceptions Editor, started by double-clicking directly on the exception name in the DB Explorer. Alternatively use the DB Explorer's right mouse-click menu item Edit Exception or key combination [Ctrl + O].

The Exception Editor appears, where changes to the exception name and exception text can be made as wished. Changes to exception texts may be made even if other objects depend on them, but not the exception name.

The SQL syntax is:

ALTER EXCEPTION <exception_name>
'New Exception Text';

An exception can be altered by its creator, the SYSDBA user, and any users with operating system root privileges.

A number of new syntaxes for changing exceptions was introduced in Firebird 2.0. Please refer to Firebird 2.0.4 Release Notes: New syntaxes for changing exceptions for further information.

back to top of page

Introduced in Firebird 2.0, CREATE OR ALTER EXCEPTION will create the exception if it does not already exist, or will alter the definition if it does, without affecting dependencies.

See also:

CREATE OR ALTER EXCEPTION

New to Firebird 2.0: The DDL statement RECREATE EXCEPTION is now available in DDL. Semantics are the same as for other RECREATE statements.

See also:

RECREATE EXCEPTION

An exception may not be dropped if it is used by other procedures or triggers, until the dependency is removed. Any such dependencies are listed on the Exception Editor's Dependencies page, where they can be directly removed, if wished.

To drop an exception use the DB Explorer right mouse-click menu item Drop Exception… or [Ctrl + Del]. IBExpert asks for confirmation:

before finally dropping the exception. Once dropped, it cannot be retrieved.

Using SQL the syntax is:

DROP EXCEPTION <exception_name>;

An exception can only be dropped by its creator, the database owner, SYSDBA, and any users with operating system root privileges.