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

时间:2015-02-22 14:57 来源:linux.it.net.cn 作者:IT

一个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)

(责任编辑：IT)

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

时间:2015-02-22 14:57 来源:linux.it.net.cn 作者:IT

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