SIGNAL SQLSTATE

The SIGNAL SQLSTATE statement is used to signal an error. It causes an error to be returned with the specified SQLSTATE and error description.

Invocation

This statement can only be used in the triggered action of a trigger.

Authorization

None required.

Syntax

SIGNAL SQLSTATE sqlstate-string-constant
    (diagnostic-string-constant)

sqlstate-string-constant

Represents an SQLSTATE. It must be a character string constant with exactly 5 characters that follow these rules for application-defined SQLSTATEs:

Each character must be from the set of digits ('0' through '9') or non-accented uppercase letters ('A' through 'Z').
The SQLSTATE class (first two characters) cannot be '00', '01' or '02' because these are not error classes.
If the SQLSTATE class (first two characters) starts with the character '0' through '6' or 'A' through 'H', the subclass (last three characters) must start with a character in the range 'I' through 'Z'.
If the SQLSTATE class (first two characters) starts with the character '7', '8', '9', or 'I' through 'Z', the subclass (last three characters) can be any of '0' through '9' or 'A' through 'Z'.

diagnostic-string-constant

A character string of up to 70 bytes that describes the error condition. If the string is longer than 70 bytes, it is truncated.

Example:

Consider a trigger for an order system that allows orders to be recorded in an ORDERS table (ORDERNO, CUSTNO, PARTNO, QUANTITY) only if there is sufficient stock in the PARTS tables. When there is insufficient stock for an order, SQLSTATE '75001' is returned along with an appropriate error description.

CREATE TRIGGER CK_AVAIL
    NO CASCADE BEFORE INSERT ON ORDERS
    REFERENCING NEW AS NEW_ORDER
    FOR EACH ROW MODE DB2SQL
    WHEN (NEW_ORDER.QUANTITY > (SELECT ON_HAND FROM PARTS 
                                    WHERE NEW_ORDER.PARTNO = PARTS.PARTNO))
        BEGIN ATOMIC
            SIGNAL SQLSTATE '75001' ('Insufficient stock for order');
        END