MYSQL触发器

实例：在test中，创建两个触发器确保用户只能把一个0～1之间的浮点数输入test数据表的percent数据列，超出这个范围的浮点数值将被替换为NULL；

USE test
CREATE TABLE test (id SERIAL, percent DOUBLE)

DELIMITER $$
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$$

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$$
DELIMITER ;


INSERT INTO test (percent) VALUES (-1),(0.3),(1.5)
 SELECT * FROM test

UPDATE test SET percent = 1.7 WHERE id = 2
SELECT * FROM test