Runtime Exception Messaging - Firebird

Some v.1.5 enhancements to exception message handling provide more options for writing exception handlers. The static exception message, defined by CREATE EXCEPTION, can be replaced with a runtime string to provide a much better context for the user to identify problem data.

In the next example, we use the v.1.5 capabilities to enforce the same rules as the two triggers in the previous example. This time, we roll the rules into a single trigger and use runtime message extensions.

This is the exception:

CREATE EXCEPTION E_REFUSE_ORDER 'Operation refused. ' ^

This is the trigger:

CREATE TRIGGER BA_SALES0 FOR SALES
ACTIVE BEFORE INSERT OR UPDATE POSITION 0 AS
DECLARE VARIABLE ORDER_STATE SMALLINT = 0;
BEGIN
IF (UPDATING AND OLD.ORDER_STATUS = 'shipped') THEN
ORDER_STATE = 1;
IF (
(EXISTS (SELECT ON_HOLD FROM CUSTOMER
WHERE CUST_NO = NEW.CUST_NO
AND ON_HOLD IS NOT NULL)
AND (INSERTING OR ORDER_STATE = 0)) THEN
ORDER_STATE = 2;
IF (ORDER_STATE = 1) THEN
EXCEPTION E_REFUSE_ORDER 'Order ' || NEW.PO_NUMBER || ' already shipped.';
ELSE
IF (ORDER_STATE = 2) THEN
EXCEPTION E_REFUSE_ORDER
'Order '|| NEW.PO_NUMBER ||'. Customer ' || NEW.CUST_NO || ' is on hold.';
END ^

In the error status array, the client will receive the name of the exception, along with the runtime message.


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

Firebird Topics