mysql>
mysql> CREATE TABLE test (id SERIAL, percent DOUBLE);
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DELIMITER $$
mysql>
mysql> CREATE TRIGGER test_before_insert
-> BEFORE INSERT ON test FOR EACH ROW
-> BEGIN
-> IF NEW.percent < 0.0 OR NEW.percent > 1.0 THEN
-> SET NEW.percent = NULL;
-> END IF;
-> END$$
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> CREATE TRIGGER test_before_update
-> BEFORE UPDATE ON test FOR EACH ROW
-> BEGIN
-> IF NEW.percent < 0.0 OR NEW.percent > 1.0 THEN
-> SET NEW.percent = NULL;
-> END IF;
-> END$$
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql>
mysql> INSERT INTO test (percent) VALUES (-1), (0.3), (1.5);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM test;
+----+---------+
| id | percent |
+----+---------+
| 1 | NULL |
| 2 | 0.3 |
| 3 | NULL |
+----+---------+
3 rows in set (0.00 sec)
mysql>
mysql> UPDATE test SET percent = 1.7 WHERE id =2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> SELECT * FROM test;
+----+---------+
| id | percent |
+----+---------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
+----+---------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)
mysql>
|