mysql触发器实例 两个触发器保证数据有效性

分享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> DELIMITER ;
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> drop table test;
Query OK, 0 rows affected (0.00 sec)