RESIGNAL
Syntax
SIGNAL [error_condition] [SET error_property [, error_property] ...]
error_condition: SQLSTATE [VALUE] 'sqlstate_value'
condition_name |
error_property: error_property_name = <error_property_value>
error_property_name: CLASS_ORIGIN
SUBCLASS_ORIGIN |
MESSAGE_TEXT |
MYSQL_ERRNO |
CONSTRAINT_CATALOG |
CONSTRAINT_SCHEMA |
CONSTRAINT_NAME |
CATALOG_NAME |
SCHEMA_NAME |
TABLE_NAME |
COLUMN_NAME |
CURSOR_NAME |
Description
RESIGNAL's syntax and semantics are very similar to SIGNAL. This statement can only be used within an error HANDLER. It produces an error, like SIGNAL. RESIGNAL clauses are the same as SIGNAL, except that they all are optional, even SQLSTATE. All the properties which are not specified in RESIGNAL, will be identical to the properties of the error that was received by the error HANDLER. For a description of the clauses, see SIGNAL.
RESIGNAL, without any clauses, produces an error which is exactly identical to the error that was received by HANDLER.
If used out of a HANDLER construct, RESIGNAL produces the following error:
ERROR 1645 (0K000): RESIGNAL when handler not active
Examples
The following procedure tries to query two tables which don't exist, producing a 1146 error in both cases. Those errors will trigger the HANDLER. The first time the error will be ignored and the client will not receive it, but the second time, the error is RESIGNALed, so the client will receive it.
CREATE PROCEDURE test_error() BEGIN DECLARE CONTINUE HANDLER FOR 1146 BEGIN IF @hide_errors IS FALSE THEN RESIGNAL; END IF; END; SET @hide_errors = TRUE; SELECT 'Next error will be ignored' AS msg; SELECT `c` FROM `temptab_one`; SELECT 'Next error won''t be ignored' AS msg; SET @hide_errors = FALSE; SELECT `c` FROM `temptab_two`; END; MariaDB [test]> CALL test_error(); +----------------------------+ | msg | +----------------------------+ | Next error will be ignored | +----------------------------+ 1 row in set (0.01 sec) +-----------------------------+ | msg | +-----------------------------+ | Next error won't be ignored | +-----------------------------+ 1 row in set (0.85 sec) ERROR 1146 (42S02): Table 'test.temptab_two' doesn't exist
The following procedure RESIGNALs an error, modifying only the error message to clarify the cause of the problem.
CREATE PROCEDURE test_error() BEGIN DECLARE CONTINUE HANDLER FOR 1146 BEGIN RESIGNAL SET MESSAGE_TEXT = '`temptab` does not exist'; END; SELECT `c` FROM `temptab`; END; MariaDB [test]> CALL test_error(); ERROR 1146 (42S02): `temptab` does not exist