SIGNAL and RESIGNAL

In this lesson we will learn how to raise errors and warnings using SIGNAL and RESIGNAL statements.

SIGNAL and RESIGNAL

SIGNAL statement is a way to return an error to a handler or a client application. The sender can choose which error characteristics (like the error number or message text) to return. SIGNAL provides a way to handle unexpected events that may lead to application termination. By raising errors, we can provide information to the error handler for a graceful exit rather than an abrupt termination.

The SIGNAL keyword is used to raise errors. It is followed by a SQLSTATE value or a named condition defined with a SQLSTATE value. SIGNAL cannot be associated with a MySQL error code. The SQLSTATE value 45000 is generic and can be used to catch any un-handled user defined exceptions.

The SET clause is used to return information about the error. The diagnostic information about an error includes MYSQL_ERRNO, MESSAGE_TEXT, CLASS_ORIGIN, SUBCLASS_ORIGIN, CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CATALOG_NAME, SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, and CURSOR_NAME. The sender can choose to send more than one of these items as a comma separated list.

Syntax

SIGNAL SQLSTATE | condition_name

SET ...

Get hands-on with 1400+ tech skills courses.