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 |
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
Notes
Example
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
CREATE TRIGGER SIGNALTRIG
BEFORE DELETE ON STOCK
SIGNAL SQLSTATE '99001'