mysql触发器检验有效性_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)