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)
总结
以上是生活随笔为你收集整理的mysql触发器检验有效性_mysql触发器实例 两个触发器保证数据有效性的全部内容，希望文章能够帮你解决所遇到的问题。
- 上一篇： mysql5.5中的MaxValue关键
- 下一篇： [顶]ORACLE PL/SQL编程详解