mysql中实现触发器插入数据前校验数据

创建表填入数据:

CREATE DATABASE test` ;

USE `test`;

DROP TABLE IF EXISTS `account`;

CREATE TABLE `account` (
  `acct_num` int(10) unsigned NOT NULL,
  `amount` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



insert  into `account`(`acct_num`,`amount`) VALUES(137,14.98),(141,1937.50),(97,-100.00);

创建触发器:

DELIMITER $$

USE `test`$$

DROP TRIGGER  `trigger`$$

CREATE

    TRIGGER `trigger` BEFORE UPDATE ON `account` 
    FOR EACH ROW     BEGIN
	IF NEW.amount < 0 THEN
		SET NEW.amount = 0;
		ELSEIF NEW.amount > 100 THEN
		SET NEW.amount = 100;
	END IF;
    END;
$$

DELIMITER ;

修改数据account如果新数字小于0，就填入0，大于100，就填入100

执行下面的语句进行测试:
UPDATE account SET amount=-10 WHERE acct_num=137;
UPDATE account SET amount=2000 WHERE acct_num=141;

可以看到：
account_num  amount
137          0.00
141          100.00
97           -100.00