How can we use SIGNAL statement with MySQL triggers?

In fact, the MySQL SIGNAL statement is an error handling mechanism used to handle unexpected events and exit gracefully from the application when needed. Basically, it provides error information to the handler. Its basic syntax is as follows -

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

Here, the SIGNAL keyword is the SQLSTATE value or the condition name declared by the DECLARE CONDITION statement. SIGNAL statements must always specify a SQLSTATE value or a named condition defined using an SQLSTATE value. The SQLSTATE value for a SIGNAL statement consists of a five-character alphanumeric code. We cannot start our own SQLSTATE code with "00" because such a value indicates success and has no effect for signaling an error. If our value is invalid, a Bad SQLSTATE error will occur. For catch all error handling we should assign SQLSTATE value "45000" which means "Unhandled user-defined exception".

To illustrate the use of SIGNAL statements with MySQL triggers, we use the following example where we create a BEFORE INSERT trigger on the Student_age table. If we try to enter an age less than 0, it will throw an error message using the SIGNAL statement.

Example

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

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

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

The above is the detailed content of How can we use SIGNAL statement with MySQL triggers?. For more information, please follow other related articles on the PHP Chinese website!