SIGNAL statement (Signal error)

Function

Generates an error and signals it; clears any information that has been assigned to the diagnostic area up to that point.

Format

SIGNAL signal-value

signal-value::={SQLSTATE-value|condition-name}
SQLSTATE-value::=SQLSTATE [VALUE] character-string-literal

Operands

Specifies the value to be returned to the UAP.

Specifies a value (combination of upper case characters A to Z, and numeric characters 0 to 9) that is valid as an SQLSTATE value, in 5 characters. Specify a value according to the following rules for SQLSTATE in HiRDB:

If the SQLSTATE class fails to comply with these rules, a definition-time error may occur. For SQLSTATE values, see SQLSTATE variable.

Specifies the condition name that was declared in the condition declaration.

If condition-name is specified, the code R0000 indicating an error (abnormal termination without an implicit rollback) is set in the SQLSTATE.

If an SQLCODE value associated with condition-name is defined, an error may occur.

Common rules

  1. Execution of the SIGNAL statement causes the value -1400 to be assigned to the SQLCODE.
  2. Execution of the RESIGNAL statement does not cause an implicit rollback. However, if the statement is executed in the trigger, an implicit rollback ensues, by excluding the tables that are defined by specifying WITHOUT ROLLBACK. Tables that are defined by specifying WITHOUT ROLLBACK are not rolled back after completion of row updating (including additions and deletions) even when the RESIGNAL statement is executed in the trigger.
  3. Execution of the SIGNAL statement clears any condition information items that were assigned to the diagnostic area before execution of the SIGNAL statement.
    In the statement information item NUMBER of the diagnostic area, the value i + 1, and the value 'N' is assigned to MORE.
    In the first (condition number 1) ERROR_SQL condition information item, the value 'REGIONAL' is assigned. If a condition name is specified in signal-value, a condition name is assigned to CONDITION_IDENTIFIER. If SQLSTATE-value is specified, 'SQLSTATE:xxxxx' (where xxxxx is the specified SQLSTATE-value) is assigned to CONDITION_IDENTIFIER.

Notes

  1. The SIGNAL statement can be specified in SQL procedures and triggers.
  2. Execution of the SIGNAL statement clears any diagnostic information that was set in the diagnostic area before execution. The RESIGNAL statement can be used to prevent the clearing of older, history diagnostic information.

Example

  1. Define an SQL procedure (STOCK_UPDATE1) that updates the quantity of a specified product code in the inventory table (STOCK). If the quantity specified in the input parameter is a negative number, use the SIGNAL statement to generate an error, and assign an Invalid value as a quantity to the output parameter.

    CREATE PROCEDURE STOCK_UPDATE1(IN UPCODE INT, IN UQUANTITY INT,
                                  OUT MSG NVARCHAR(50))
    BEGIN
     DECLARE illegal_value CONDITION ;
     DECLARE EXIT HANDLER FOR illegal_value
       SET MSG=N'Invalid value as a quantity';
     DECLARE EXIT HANDLER FOR NOT FOUND
       SET MSG=N'Specified product code not found.';
     IF UQUANTITY<0 THEN
       SIGNAL illegal_value;
     ELSE
       UPDATE STOCK SET SQUANTITY=UQUANTITY WHERE SPCODE=UPCODE;
       SET MSG=N'Updating completed'.;
     END IF;
    END

  2. Before deleting rows in the inventory table (STOCK), use the SIGNAL statement to generate an error, and define a trigger (SIGNALTRIG) that suppresses the deletion of rows from the inventory table.:

    CREATE TRIGGER SIGNALTRIG
     BEFORE DELETE ON STOCK
     SIGNAL SQLSTATE '99001'