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.
The maximum size of an exception message was raised in Firebird 2.0 from 78 to 1021 bytes.
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.
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.
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
When an exception is raised, the following takes place:
- The exception terminates the trigger or procedure.
- Any statements in the trigger or stored procedure that follow the
EXCEPTIONstatement are not executed. In the case of a
BEFOREtrigger the update that fired the trigger is aborted.
- 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
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, however 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.
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.
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.