Description:
In this trigger,
create trigger mme before insert on uue for each row if new.ll = 0 then
signal sqlstate '22003' set message_text = 'I hate 0';
elseif new.ll < 0 then
set @s = 'qfusq';
signal sqlstate '01508' set message_text = 'Negativs are bad';
end if
when new.ll < 0 there is no trace in the output that the signal was done:
mysql> create table uue (ll int);
Query OK, 0 rows affected (0.03 sec)
mysql> delimiter ?
mysql> create trigger mme before insert on uue for each row if new.ll = 0 then
-> signal sqlstate '22003' set message_text = 'I hate 0';
-> elseif new.ll < 0 then
-> set @s = 'qfusq';
-> signal sqlstate '01508' set message_text = 'Negativs are bad';
-> end if ?
Query OK, 0 rows affected (0.08 sec)
mysql> delimiter ;
mysql> insert into uue value (0);
ERROR 1644 (22003): I hate 0
mysql> set @s = 'mlavh';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into uue value (-1);
Query OK, 1 row affected (0.03 sec)
mysql> show warnings;
Empty set (0.00 sec)
mysql> select @s;
+-------+
| @s |
+-------+
| qfusq |
+-------+
1 row in set (0.02 sec)
mysql> set sql_mode='traditional';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s = 'wweww';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into uue value (-1);
Query OK, 1 row affected (0.02 sec)
mysql> show warnings;
Empty set (0.00 sec)
mysql> select @s;
+-------+
| @s |
+-------+
| qfusq |
+-------+
1 row in set (0.00 sec)
mysql> select * from uue;
+------+
| ll |
+------+
| -1 |
| -1 |
+------+
2 rows in set (0.00 sec)
mysql> signal sqlstate '01508' set message_text = 'Negativs are bad';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------+
| Level | Code | Message |
+---------+------+------------------+
| Warning | 1642 | Negativs are bad |
+---------+------+------------------+
1 row in set (0.00 sec)
mysql> signal sqlstate '22003' set message_text = 'I hate 0';
ERROR 1644 (22003): I hate 0
mysql> show warnings;
+-------+------+----------+
| Level | Code | Message |
+-------+------+----------+
| Error | 1644 | I hate 0 |
+-------+------+----------+
1 row in set (0.00 sec)
How to repeat:
create table uue (ll int);
delimiter ?
create trigger mme before insert on uue for each row if new.ll = 0 then
signal sqlstate '22003' set message_text = 'I hate 0';
elseif new.ll < 0 then
set @s = 'qfusq';
signal sqlstate '01508' set message_text = 'Negativs are bad';
end if ?
delimiter ;
insert into uue value (0);
set @s = 'mlavh';
insert into uue value (-1);
show warnings;
select @s;
set sql_mode='traditional';
set @s = 'wweww';
insert into uue value (-1);
show warnings;
select @s;
select * from uue;
signal sqlstate '01508' set message_text = 'Negativs are bad';
show warnings;
signal sqlstate '22003' set message_text = 'I hate 0';
show warnings;
Suggested fix:
Let all signals from triggers be reported as if directly entered, although the running not be interrupted.
Description: In this trigger, create trigger mme before insert on uue for each row if new.ll = 0 then signal sqlstate '22003' set message_text = 'I hate 0'; elseif new.ll < 0 then set @s = 'qfusq'; signal sqlstate '01508' set message_text = 'Negativs are bad'; end if when new.ll < 0 there is no trace in the output that the signal was done: mysql> create table uue (ll int); Query OK, 0 rows affected (0.03 sec) mysql> delimiter ? mysql> create trigger mme before insert on uue for each row if new.ll = 0 then -> signal sqlstate '22003' set message_text = 'I hate 0'; -> elseif new.ll < 0 then -> set @s = 'qfusq'; -> signal sqlstate '01508' set message_text = 'Negativs are bad'; -> end if ? Query OK, 0 rows affected (0.08 sec) mysql> delimiter ; mysql> insert into uue value (0); ERROR 1644 (22003): I hate 0 mysql> set @s = 'mlavh'; Query OK, 0 rows affected (0.00 sec) mysql> insert into uue value (-1); Query OK, 1 row affected (0.03 sec) mysql> show warnings; Empty set (0.00 sec) mysql> select @s; +-------+ | @s | +-------+ | qfusq | +-------+ 1 row in set (0.02 sec) mysql> set sql_mode='traditional'; Query OK, 0 rows affected (0.00 sec) mysql> set @s = 'wweww'; Query OK, 0 rows affected (0.00 sec) mysql> insert into uue value (-1); Query OK, 1 row affected (0.02 sec) mysql> show warnings; Empty set (0.00 sec) mysql> select @s; +-------+ | @s | +-------+ | qfusq | +-------+ 1 row in set (0.00 sec) mysql> select * from uue; +------+ | ll | +------+ | -1 | | -1 | +------+ 2 rows in set (0.00 sec) mysql> signal sqlstate '01508' set message_text = 'Negativs are bad'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+------------------+ | Level | Code | Message | +---------+------+------------------+ | Warning | 1642 | Negativs are bad | +---------+------+------------------+ 1 row in set (0.00 sec) mysql> signal sqlstate '22003' set message_text = 'I hate 0'; ERROR 1644 (22003): I hate 0 mysql> show warnings; +-------+------+----------+ | Level | Code | Message | +-------+------+----------+ | Error | 1644 | I hate 0 | +-------+------+----------+ 1 row in set (0.00 sec) How to repeat: create table uue (ll int); delimiter ? create trigger mme before insert on uue for each row if new.ll = 0 then signal sqlstate '22003' set message_text = 'I hate 0'; elseif new.ll < 0 then set @s = 'qfusq'; signal sqlstate '01508' set message_text = 'Negativs are bad'; end if ? delimiter ; insert into uue value (0); set @s = 'mlavh'; insert into uue value (-1); show warnings; select @s; set sql_mode='traditional'; set @s = 'wweww'; insert into uue value (-1); show warnings; select @s; select * from uue; signal sqlstate '01508' set message_text = 'Negativs are bad'; show warnings; signal sqlstate '22003' set message_text = 'I hate 0'; show warnings; Suggested fix: Let all signals from triggers be reported as if directly entered, although the running not be interrupted.