How can we use SIGNAL statement with MySQL triggers?

Actually, MySQL SIGNAL statement is an error handling mechanism for handling unexpected occurrences and a graceful exit from the application if need to be. Basically, it provides error information to the handler. Its basic syntax would be as follows −

SIGNAL SQLSTATE | condition_value
[SET signal_information_item = value_1,[, signal_information_item] = value_2,
etc;]

Here, the SIGNAL keyword is an SQLSTATE value or a condition name declared by a DECLARE CONDITION statement. The SIGNAL statement must always specify an SQLSTATE value or a named condition that defined with an SQLSTATE value. The SQLSTATE value for a The SIGNAL statement consists of a five-character alphanumeric code. We must not start our own SQLSTATE code with '00' because such values indicate success and are not valid for signaling an error. If our value is invalid, a Bad SQLSTATE error occurs. For catch-all error handling, we should assign an SQLSTATE value of '45000', which signifies an “unhandled user-defined exception.”

To illustrate the use of SIGNAL statement with MySQL triggers we are using the following example in which we are creating a BEFORE INSERT trigger on the student_age table. It will throw an error message using SIGNAL statement if we will try to enter the age of less than 0.

Example

mysql> Create trigger before_insert_studentage BEFORE INSERT on
student_age FOR EACH ROW BEGIN IF NEW.age<0 then SIGNAL SQLSTATE
'45000' SET MESSAGE_TEXT = 'age less than 0'; END if; END; //
Query OK, 0 rows affected (0.12 sec)

mysql> Insert into student_age(age, name) values(-10,'gaurav')//
ERROR 1644 (45000): age less than 0

It is clear from the above result set that if we will try to insert age less than 0 then it will throw an error using SIGNAL statement.