Exceptions in Action - Firebird

The internally defined exceptions are thrown by the engine in response to corresponding errors that require execution to stop. They cover a very large range of conditions, including every kind of constraint violation, arithmetic and string overflows, references to missing objects, data corruption, and so on. The SQLCODE and GDSCODE exceptions are the same ones that are used when errors occur during dynamic SQL operations.

Custom exceptions, which are available only in PSQL modules, do not need to duplicate the work of the internally defined ones. Define your exceptions for use when you want your code to detect error conditions that break your business rules. The three exception types are depicted in Figure.

Standard PSQL response to exceptions

Standard PSQL response to exceptions

We encountered an example in Chapter Stored Procedures in which a custom exception was used in a trigger for the purpose of stopping an event, where letting it continue would break a business rule. In this case, a stored procedure was taking care of tidying up dependencies left in the organizational structure by the departure of an employee. It was declared like this:

CREATE EXCEPTION REASSIGN_SALES
'Reassign the sales records before deleting this employee.' ^
COMMIT ^

At the point where the exception is to be used, the procedure checks whether the employee appears as the sales rep on any outstanding sales orders. If so, the custom exception is used to end the procedure. Of course, the exception, if it occurs, causes all of the other tasks performed by the procedure to be undone.

...
BEGIN
IF (EXISTS(SELECT PO_NUMBER FROM SALES
WHERE SALES_REP = :emp_num)) THEN
EXCEPTION reassign_sales;

There are cases where it is possible to use the custom exception as a way to intervene, deal with a problem condition, and let the procedure continue. We can trap the exception and write code to handle it, right there in the procedure. The next section examines how this trap-and-fix technique can be used to deal with our reassign_sales exception.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics