Details
Major Description
This issue was found and reported by ndiamond on stackoverflow.
According to the SQL Standard SQLSTATE codes beginning with 01 are treated as warnings instead of errors.
SIGNAL SQLSTATE '01000';
|
Query OK, 0 rows affected, 1 warning (0.000 sec)
|
|
|
SHOW WARNINGS;
|
+---------+------+------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+------------------------------------------+
|
| Warning | 1642 | Unhandled user-defined warning condition |
|
+---------+------+------------------------------------------+
|
While the example above works as expected, the same SIGNAL inside a trigger has no effect:
DDL:
CREATE OR REPLACE TABLE t1 (a int);
|
CREATE OR REPLACE TRIGGER test_warning
|
BEFORE INSERT ON t1
|
FOR EACH ROW
|
BEGIN
|
IF NEW.a < 0
|
THEN
|
SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'Negative value inserted';
|
END IF;
|
END
|
Test:
insert into t1 values (-1);
|
Query OK, 1 row affected (0.001 sec)
|
|
|
show warnings;
|
Empty set (0.000 sec)
|