- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can we use SIGNAL statement with MySQL triggers?
152 Lectures 16 hours
87 Lectures 5.5 hours
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.
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.
- How can we use a MySQL subquery with INSERT statement?
- Can we use ADD and CHANGE with ALTER Statement in MySQL?
- Can we use WHERE clause inside MySQL CASE statement?
- Can we use Switch statement with Strings in java?
- How we can find all the triggers associated with a particular MySQL table?
- Can we use SELECT NULL statement in a MySQL query?
- How can we get the metadata of triggers?
- Why should we use MySQL CASE Statement?
- How can we emulate CHECK CONSTRAINT by using triggers?
- In MySQL, how we can write Multiple-line statement?
- How can we escape special characters in MySQL statement?
- How can we enter BOOLEAN values in MySQL statement?
- How can we use MySQL INSTR() function with WHERE clause?
- How can we use MySQL SUM() function with HAVING clause?
- How can we use a MySQL subquery with FROM clause?