分享mysql觸發(fā)器的例子，代碼如下：

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 //創(chuàng)建觸發(fā)器 插入數(shù)據(jù)

-> 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 //創(chuàng)建觸發(fā)器 更新數(shù)據(jù)

-> 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); //插入數(shù)據(jù)

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; //更新數(shù)據(jù)

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)

總結(jié)

以上是生活随笔為你收集整理的mysql触发器检验有效性_mysql触发器实例 两个触发器保证数据有效性的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。