SQL Reference

SIGNAL SQLSTATE

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

Invocation

The SIGNAL SQLSTATE statement can only be used as a triggered SQL statement within a trigger.

Authorization

No authorization is required to execute this statement.

Syntax

>>-SIGNAL--SQLSTATE--string-constant---(--diagnostic-string--)--><
 

Description

string-constant

The specified string-constant represents an SQLSTATE. It must be a character string constant with exactly 5 characters that follow the rules for application-defined SQLSTATEs as follows:

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

If the SQLSTATE does not conform to these rules an error occurs (SQLSTATE 428B3).

diagnostic-string

An expression with a type of CHAR or VARCHAR that returns a character string of up to 70 bytes that describes the error condition. If the string is longer than 70 bytes, it will be truncated.

Example

Consider an order system that records orders in an ORDERS table (ORDERNO, CUSTNO, PARTNO, QUANTITY) only if there is sufficient stock in the PARTS tables.

 CREATE TRIGGER check_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